从jsonb postgres获取值

0pizxfdo 于 2022-09-21 发布在 PostgreSQL

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

我得到了以下jsonb，我想在其中从键中检索值：type、atus和id。我试了以下几种方法，但不成功？不知道哪里出了问题。有谁能帮我吗？提前Tnx！

with raw_data(col) as 
(
select '{
    "tools": [
        {
            "type": "toolA",
            "links": {
                "get": {
                    "url": "/toolAget",
                    "method": "GET"
                },
                "post": {
                    "url": "/toolAPost",
                    "method": "POST"
                }
            },
            "status": "ACTIVE",
            "id": "7891000"
        },
        {
            "type": "toolB",
            "links": {
                "get": {
                    "url": "/toolBget",
                    "method": "GET"
                },
                "post": {
                    "url": "/toolBPost",
                    "method": "POST"
                }
            },
            "status": "INACTIVE",
            "id": "123456"
        }
    ]
}'::jsonb
)
select 
col -> 'tools' -> 'type',
col -> 'tools' -> 'status',
col -> 'tools' -> 'id'
from raw_data;

postgresql

来源：https://stackoverflow.com/questions/73763798/get-values-from-jsonb-postgres

1条答案

按热度按时间

gajydyqb1#

使用jsonb_extract_path获取tools阵列，然后使用jsonb_to_recordset获取id、type和status值。

SELECT
    *
FROM
    jsonb_to_recordset(jsonb_extract_path('{
    "tools": [
        {
            "type": "toolA",
            "links": {
                "get": {
                    "url": "/toolAget",
                    "method": "GET"
                },
                "post": {
                    "url": "/toolAPost",
                    "method": "POST"
                }
            },
            "status": "ACTIVE",
            "id": "7891000"
        },
        {
            "type": "toolB",
            "links": {
                "get": {
                    "url": "/toolBget",
                    "method": "GET"
                },
                "post": {
                    "url": "/toolBPost",
                    "method": "POST"
                }
            },
            "status": "INACTIVE",
            "id": "123456"
        }
    ]
}'::jsonb, 'tools')) AS x (id integer,
        type varchar,
        status varchar);

 id     | type  |  status  
---------+-------+----------
7891000 | toolA | ACTIVE
123456  | toolB | INACTIVE

赞(0）回复(0）举报 2022-09-21

我来回答

从jsonb postgres获取值

1条答案

相关问题

热门标签

最新问答