查询JSONB中的对象数组

本文介绍了查询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.

在线示例