POSTGRESQL extract keys from jsonb

Question

I have a table with a jsonb column that contains a lot of external data and I need to extract some keys, but I'm having a hard time doing it.

The table is like that:

code title external_data G20540 Data Analysis

The external data is the jsonb column, organized with a dict inside an array and it has these infos:

[
    {
        "DESCR": "Requisito RJ_GRD_GCSOSRJ",
        "ORDERNO": "10",
        "ACAD_PLAN": "",
        "ACAD_PROG": "",
        "DESCR254A": "Requisito RJ_GRD_GCSOSRJ_Plan. de Comunicação I",
        "DESCRSHORT": "Requisito",
        "EFF_STATUS": "A",
        "RQ_CONNECT": "",
        "ACAD_CAREER": "",
        "INSTITUTION": "X",
        "PARENTHESIS": "",
        "SAA_DESCR80": "Requisito RJ_GRD_GCSOSRJ_Plan. de Comunicação I",
        "RQRMNT_GROUP": "000312",
        "ACAD_SUB_PLAN": "",
        "CREATION_DATE": "2020-04-13T21:26:51.923",
        "RQRMNT_USEAGE": "ENR",
        "CONDITION_CODE": "CRS",
        "CONDITION_DATA": "003130",
        "REQUISITE_TYPE": "PRE",
        "CONDITION_DESCR": "ID Curso",
        "RQRMNT_LIST_SEQ": "1",
        "RQ_GRP_LINE_NBR": "0010",
        "RQ_LINE_KEY_NBR": "0001",
        "RQ_GRP_LINE_TYPE": "CRSE",
        "CONDITION_OPERATOR": "EQ"
    }
]

I need to extract "DESCR", "ORDENO", "DESCR254A", "SAA_DESCR80", "RQRMNT_GROUP", "RQRMNT_USEAGE", "EFF_STATUS".

I tried with this query but I get only null results for the external_data columns:

SELECT codes.external_id as "code"
    ,codes.title as "title"
    ,requirements.external_data ->> 'RQRMNT_GROUP' as "RQRMNT_GROUP"
    ,requirements.external_data ->> 'EFF_STATUS' as "EFF_STATUS"
    ,requirements.external_data ->> 'RQRMNT_USEAGE' as "RQRMNT_USEAGE"
    ,requirements.external_data ->> 'DESCR' as "DESCR"
    ,requirements.external_data ->> 'SAA_DESCR80' as "SAA_DESCR80"
    ,requirements.external_data ->> 'DESCR254A' as "DESCR254A"
    ,requirements.external_data ->> 'ORDERNO' as "ORDERNO"
FROM requirements
LEFT JOIN codes ON codes.id = requirements.code_id

How can I do that?

Your JSON value is an array. You need to use -> 0 ->> 'RQRMNT_GROUP' to access the first array element. What do you want as a result if the array contains more than one element — user330315
– user330315, Commented Jul 7, 2021 at 13:30

Brad · Accepted Answer · 2021-07-07 13:44:32Z

1

You'll need to use a WITH clause to identify the table in the array and select from that:

WITH external_codes AS (
   SELECT r.code_id,
          jsonb_array_elements(r.external_data) AS external_data
   FROM requirements r
)
SELECT codes.external_id as "code"
    ,codes.title as "title"
    ,external_codes.external_data ->> 'RQRMNT_GROUP' as "RQRMNT_GROUP"
    ,external_codes.external_data ->> 'EFF_STATUS' as "EFF_STATUS"
    ,external_codes.external_data ->> 'RQRMNT_USEAGE' as "RQRMNT_USEAGE"
    ,external_codes.external_data ->> 'DESCR' as "DESCR"
    ,external_codes.external_data ->> 'SAA_DESCR80' as "SAA_DESCR80"
    ,external_codes.external_data ->> 'DESCR254A' as "DESCR254A"
    ,external_codes.external_data ->> 'ORDERNO' as "ORDERNO"
FROM external_codes
LEFT JOIN codes ON codes.id = external_codes.code_id

answered Jul 7, 2021 at 13:44

Brad

2,3293 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

POSTGRESQL extract keys from jsonb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related