如何在postgres中过滤json嵌套键的值

我在PostgreSQL中有一个表

CREATE TABLE IF NOT EXISTS account_details
(
    account_id integer,
    condition json
)

此表中的数据包括
| 帐户标识|条件|
| - ------|- ------|
| 1个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[35，20，5，6 ]}}]|
| 第二章|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[1，4，2，3 ]}}]|
| 三个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[5 ]}}}]|
| 四个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[1，2，3，5 ]}}]|
| 五个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[3，4 ]}}]|
| 六个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[4 ]}}}]|
| 七|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[3 ]}}}]|
我需要获取rootcompanyid中存在的部分值
请指导我如何过滤嵌套JSON数据

- 预期产出**
- 病例：1例**

每当我向下面的查询传递输入rootcompanyid = 5时，它应该过滤掉account_id 1、3和4的详细信息

SELECT *
  FROM account_details
 WHERE ((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in' = 5;

| 帐户标识|条件|
| - ------|- ------|
| 1个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[35，20，5，6 ]}}]|
| 三个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[5]}}]|
| 四个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[1，2，3，5]}}]|

- 案件：2起**

每当我向下面的查询传递输入rootcompanyid = 3，4时，它应该过滤掉account_id 2、4、5、6和7的详细信息

SELECT *
  FROM account_details
 WHERE ((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in' = 3,4;

| 帐户标识|条件|
| - ------|- ------|
| 第二章|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[1，4，2，3]}}]|
| 四个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[1，2，3，5 ]}}]|
| 五个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[3，4]}}]|
| 六个|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[4]}}}]|
| 七|[{"操作"："阅读"，"主题"："根公司"，"条件"：{"根公司ID"：{" $in "：[3]}}]|
请给我最好的解决方案来过滤JSON格式的部分和匹配的rootcompanyid

您需要比较嵌套json中的元素。Here是一个示例：

SELECT *
  FROM account_details
 WHERE ((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in' = '[35,20,5,6]';

condition->0-数组的第一个元素
->>'conditions')::json-从数组的第一个元素提取conditions
->>'rootcompanyid')::json-从conditions中提取rootcompanyid
::json->>'$in'-从rootcompanyid中提取$in（rootcompanyid的ID）
按操作搜索：

SELECT *
  FROM account_details
 WHERE condition->0->>'action' = 'read';

- 更新**

要在json array中搜索特定的id，您可以使用@>运算符：

SELECT *
  FROM account_details
 WHERE ((((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in')::jsonb) @> '[3,1]'::jsonb

或LIKE：

SELECT *
  FROM account_details
 WHERE ((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in' LIKE '%5%';

- 更新版本2**

您可以使用json_array_elements_text + exists：

WITH parsed_conditions AS (
    SELECT account_id,
           json_array_elements_text(
               (((condition -> 0 ->> 'conditions')::json ->> 'rootcompanyid')::json ->>'$in')::json
           ) AS json_id
      FROM account_details
)

SELECT *
  FROM account_details AS a
 WHERE exists(
    SELECT DISTINCT account_id
      FROM parsed_conditions
     WHERE json_id IN ('3', '1')
       AND parsed_condidtions.account_id = a.account_id
);

如何在postgres中过滤json嵌套键的值

1条答案

相关问题

热门标签

最新问答