包含特定键值的json对象中的PostgreSQL查询行

Question

# 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

# 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 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" }}]');

>

检查Postgres JSON数组是否包含字符串

在postgreSQL中从表中查询json对象

带有嵌入JSON对象的PostgreSQL JSON查询

如何实现这些查询？我相信如果我知道如何查询一个问题，那么我将能够解决其他问题。

我将提供任何可能需要的信息来解决这个问题。

Answer 1

官方的Postgresql文档包含您所需的所有内容。

根据您的问题，请参阅以下查询：

1. 如何查询列msg包含的数据，其中类型的键值为msgsend？

select * from tx where msg->0->>'type' = 'MsgSend';

select * from tx where msg->0->'value'->>'from' = 'Jeniffer';

select * from tx where (msg->0->'value'->'amount'->0->>'amount')::int > 50;