get values from jsonb postgres

Niels Groeneveld 0
Viewed 17

I got the following jsonb, where I want retrieve the values from the keys: type, status and id. I tried the following but it wont succeed? not sure where it goes wrong. Somebody who could help me out? Tnx in advance!

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 json
1 Answers

Using jsonb_extract_path to fetch the tools array and then jsonb_to_recordset to pull out the id, type and status values.

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
Adrian Klaver0
Related