我有一个表,该表的列的数据类型为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中的对象数组
发布评论