CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3jsonb
JayB Kim
JayB Kim

Reputation: 325

PostgreSQL query rows in json object that contains certain key value

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.

Upvotes: 3

Views: 4927

Answers (1)

Tomer Sela
Tomer Sela

Reputation: 491

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;

Upvotes: 3

Related Questions