查询JSONB中的对象数组 [英] Querying an array of objects in JSONB

问题描述

我有一个表，该表的列的数据类型为JSONB.列中的每一行都有一个类似于以下内容的JSON:

I have a table with a column of the data type JSONB. Each row in the column has a JSON that looks something like this:

[
  {
    "A":{
      "AA": "something",
      "AB": false
    }
  },
  {
    "B": {
      "BA":[
        {
          "BAAA": [1,2,3,4]
        },
        {
          "BABA": {
           .... 
          }
        }
      ]
    }
  }
]

注意:JSON是一堆完整的列表和对象，共有300行.不是我的数据，但我坚持使用它. :(

Note: the JSON is a complete mess of lists and objects, and it has a total of 300 lines. Not my data but I am stuck with it. :(

我正在使用PostgreSQL版本12

I am using postgresql version 12

我将如何编写以下查询:

How would I write the following queries:

• 返回所有AB值设置为false的行.
• 返回BAAA的值是每一行.

推荐答案

您可以通过JSON路径查询找到AB = false行:

You can find the AB = false rows with a JSON Path query:

select *
from test
where data @@ '$[*].A.AB == false'

如果您不知道键AB的确切位置，可以使用:

If you don't know where exactly the key AB is located, you can use:

select *
from test
where data @@ '$[*].**.AB == false'

---

要将数组中的所有元素显示为行，可以使用:

---

To display all elements from the array as rows, you can use:

select id, e.*
from test
  cross join jsonb_array_elements(jsonb_path_query_first(data, '$[*].B.BA.BAAA')) with ordinality as e(item, idx)

我在列"id"中添加了作为主键列的占位符，以便可以在输出中确定数组元素的来源.

I include a column "id" as a placeholder for the primary key column, so that the source of the array element can be determined in the output.

在线示例

这篇关于查询JSONB中的对象数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！