时间戳

首页 > 解决方案 > 基于JSON数组子元素的查询

sql - 基于JSON数组子元素的查询

问题描述

从这里和其他地方尝试了多个答案，但还没有找到正确的答案。

create table mstore (
muuid uuid PRIMARY KEY,
msid text,
m_json JSONb[] not NULL
);

插入第一行：

insert into mstore (muuid, msid, m_json) values (
'3b691440-ee54-4d9d-a5b3-5f1863b78755'::uuid,
'<163178891004.4772968682254423915@XYZ-73SM>',
(array['{"m": 123, "mts": "2021-09-16T10:53:43.599012", "dstatus": "Dropped", "rcpt": "abc1@xyz.com"}']::jsonb[])
);

插入第二行：

insert into mstore (muuid, msid, m_json) values (
'3b691440-ee54-4d9d-a5b3-5f1863b78757'::uuid,
'<163178891004.4772968682254423915@XYZ-75SM>',
(array['{"m": 125, "mts": "2021-09-16T10:53:43.599022", "dstatus": "Dropped", "rcpt": "abc3@xyz.com"}']::jsonb[])
);

更新了第一行：

update mstore
set m_json = m_json || '{"m": 124, "mts": "2021-09-16T10:53:43.599021", "dstatus": "Delivered", "rcpt": "abc2@xyz.com"}'::jsonb
where muuid = '3b691440-ee54-4d9d-a5b3-5f1863b78755';

现在表格看起来像：

               muuid               |                          msid                          |                                                                                                                 m_json                                                                                                                  
--------------------------------------+----------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 3b691440-ee54-4d9d-a5b3-5f1863b78757 | <163178891004.4772968682254423915@XYZ-75SM> | {"{\"mid\": 125, \"rcpt\": \"abc3@xyz.com\", \"msg_ts\": \"2021-09-16T10:53:43.599022\", \"dstatus\": \"Dropped\"}"}
 3b691440-ee54-4d9d-a5b3-5f1863b78755 | <163178891004.4772968682254423915@XYZ-73SM> | {"{\"mid\": 123, \"rcpt\": \"abc1@xyz.com\", \"msg_ts\": \"2021-09-16T10:53:43.599012\", \"dstatus\": \"Dropped\"}","{\"mid\": 124, \"rcpt\": \"abc2@xyz.com\", \"msg_ts\": \"2021-09-16T10:53:43.599021\", \"dstatus\": \"Delivered\"}"}

现在，我需要根据状态进行查询。我尝试了一些但最相关的一个是

select * from mstore,jsonb_array_elements(m_json) with ordinality arr(item_object, position)  where item_object->>'{"dstatus": "Delivered"}';

和

select * from mstore where m_json @> '[{"dstatus": "Delivered"}]';

两者都不起作用，因为它们有语法错误。如何使用 dstatus 值运行此查询？

标签： sqlarraysjsonpostgresqlpsql

解决方案

请注意，这mstore.m_json是 JSONB 元素的 Postgres 数组，而不是 JSONB 数组，因此unnest必须使用而不是jsonb_array_elements. 还可以查看文档->>中的操作员。 这同样适用于您的第二个示例。如果是 JSONB 数组而不是 JSONB 元素的 Postgres 数组，它将起作用。
mstore.m_json

select m.muuid, m.msid, l.item_object, l.pos
from mstore m
cross join lateral unnest(m.m_json) with ordinality l(item_object, pos)
where l.item_object ->> 'dstatus' = 'Delivered';

最好对列使用 JSONB 数据类型mstore.m_json而不是 JSONB[]，或者更好地规范数据设计。

推荐阅读