json Postgresql -如何查询嵌套数组元素

hgtggwj0 于 2023-02-10 发布在 PostgreSQL

关注(0)|答案(1)|浏览(281)

我在PostgreSQL 13表中有JSON数组数据。我想查询此表以查看输出中的所有嵌套数组数据。我尝试了以下查询，但它没有给出预期的输出。

select 
json_data::json -> 'Rows' -> 0 -> 'Values' ->> 0 as Lid
,json_data::json -> 'Rows' -> 0 -> 'Values' ->> 1 as L2LicenseId
,json_data::json -> 'Rows' -> 1 -> 'Values' ->> 0 as Lid
,json_data::json -> 'Rows' -> 1 -> 'Values' ->> 1 as L2LicenseId
from test;

有人能帮帮我吗？

- 示例数据**

CREATE TABLE IF NOT EXISTS test
(
    json_data text 
);

INSERT INTO test (json_data) VALUES ('{"Origin":"api","Topic":"licenses","Timestamp":"2023-02-07T12:46:42.2568898+00:00","Columns":["LId","L2LicenseId","SfdcAccountId","SfdcLineItemId","SL","Quantity","StartDate","EndDate","DisplayName","ProductPrimaryKey"],"Schema":["string","string","string","string","string","int32","datetime","datetime","string","string"],"Rows":[{"Values":["1234","123456","ACC_","PurchaseT","SKU-0000","1","2023-01-09T00:00:00.0000000","2024-01-08T00:00:00.0000000","Automation with 5 users","lc11dev.my-dev.com"]},{"Values":["8967","8967-e567","fihikelo","Addon_00000490_2nd_GB","SKU-0490","3","2023-01-01T00:00:00.0000000","2023-01-22T00:00:00.0000000","Automation, Data 5GB","mygreattest01311433.my-dev.com"]}]}')

预期输出

DB FIDDLE

JSON

来源：https://stackoverflow.com/questions/75386130/postgresql-how-to-query-nested-array-elements

1条答案

按热度按时间

zpgglvta1#

你可以使用PostgreSQL的jsonb_array_elements（或json_array_elements）函数来实现，这个函数提取所有的Json数组元素，就像行视图一样。

select 
    a2.value -> 'Values' ->> 0 as Lid, 
    a2.value -> 'Values' ->> 1 as L2LicenseId
from test a1
cross join jsonb_array_elements(a1.json_data::jsonb->'Rows') a2

-- Result: 
lid  | l2licenseid |
--- -+-------------+
1234 | 123456      |
8967 | 8967-e567   |

赞(0）回复(0）举报 2023-02-10

我来回答

json Postgresql -如何查询嵌套数组元素

1条答案

相关问题

热门标签

最新问答