3

I have this sample table called tx that stores information about transactions, and I am using PostgreSQL 10.6. 

# info about my PostgreSQL version
select version()
> PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

It looks like this:

# create table
create table tx (id bigserial primary key, msg jsonb not null);

# create index on 'type' value of msg column
create index on tx using gin ((msg->'type'));

Insert rows into tx table

insert into tx (msg) values
('[{"type": "MsgSend", "value": {"amount": [{"denom": "dollar", "amount": "100"}], "from": "Jeniffer", "to": "James" }}]'),
('[{"type": "MsgSend", "value": {"amount": [{"denom": "dollar", "amount": "30"}], "from": "Jeniffer", "to": "James" }}]'),
('[{"type": "MsgBuy", "value": {"amount": [{"denom": "dollar", "amount": "10"}], "from": "George", "to": "Smith" }}]'),
('[{"type": "MsgSend", "value": {"amount": [{"denom": "dollar", "amount": "60"}], "from": "Jeniffer", "to": "James" }}]');

I have read this Querying JSON (JSONB) data types in PostgreSQL and searched similar posts and tested with any given examples, but they don't seem to guide me well to solve what I am trying to accomplish, which is to query rows in json object, not json array

These posts that

How do I go about achieving these queries? I believe if I were to know how to query one, then I would be able to solve the other questions.

  1. How do I query data where column msg contains where key value of type is MsgSend?

  2. How do I query data where column msg contains where key value of from is Jeniffer?

  3. How do I query data where column msg contains MsgSend and amount is greater than 50?

I will be providing any information that may be needed to figure out this question.

JayB Kim
  • 327
  • 5
  • 16
  • 9.3 is no longer supported, and JSON support has advanced since then. Use a newer version. – jjanes Dec 23 '19 at 13:53
  • Why do you have your JSON objects wrapped in gratuitous arrays of length 1? Can you fix that, or is it non-negotiable? – jjanes Dec 23 '19 at 13:55
  • @jjanes Thanks for your comments. It is non-negotiable. Looks like Tomer Sela replied below, which was what I needed to know. – JayB Kim Dec 26 '19 at 01:56

1 Answers1

3

The official Postgresql documentation contains all you need.

See below queries per your questions:

  1. How do I query data where column msg contains where key value of type is MsgSend?
select * from tx where msg->0->>'type' = 'MsgSend';
  1. How do I query data where column msg contains where key value of from is Jeniffer?
select * from tx where msg->0->'value'->>'from' = 'Jeniffer';
  1. How do I query data where column msg contains MsgSend and amount is greater than 50?
select * from tx where (msg->0->'value'->'amount'->0->>'amount')::int > 50;
Tomer Sela
  • 481
  • 9
  • 16