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.
How do I query data where column
msgcontains where key value oftypeisMsgSend?How do I query data where column
msgcontains where key value offromisJeniffer?How do I query data where column
msgcontainsMsgSendand amount isgreater than 50?
I will be providing any information that may be needed to figure out this question.