sqljsonpostgresqlset-returning-functions

Unnest objects from a jsonb array into separate rows

I have a table in Postgres 14.9:

Name (Txt) Detail (JSONB) state (JSONB)
apple [{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }] [{ "ap": "good", "op2": "bad" }]
orange [{ "code": "156", "color": "red" }, { "code": "235", "color": "blue" }] [{ "op": "bad", "op2": "best" }]
lemon [{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }] [{ "cp": "best", "op2": "good" }]

I want this select sql output :

Name (Txt) Detail (JSONB) state (JSONB)
apple { "code": "156", "color": "red"} { "ap": "good", "op2": "bad" }
apple { "code": "156", "color": "blue"} { "ap": "good", "op2": "bad" }
orange { "code": "156", "color": "red" } { "op": "bad", "op2": "best" }
lemon { "code": "156", "color": "red" } { "cp": "best", "op2": "good" }
lemon { "code": "156", "color": "blue"} { "cp": "best", "op2": "good" }

My attempt:

SELECT
    "Name (Txt)"
 ,  jsonb_build_object('code', elem->>'code', 'color', elem->>'color')  AS "Detail (JSONB)"
 ,  state::JSONB "
FROM your_table,
     jsonb_array_elements("Detail (JSONB)") AS elem,
     state::JSONB
WHERE elem->>'code' = '156';
Solution

  • You could unnest your array with JSONB_TO_RECORDSET, then remap your json objects with JSONB_BUILD_OBJECT.

    SELECT tab.name, 
       JSONB_BUILD_OBJECT('code', cte.code, 'color', cte.color) AS "Detail (JSONB)",
       tab.state AS "State (JSONB)" 
FROM       tab
CROSS JOIN JSONB_TO_RECORDSET(tab.Detail) cte(  
               code  VARCHAR(30),
               color VARCHAR(30)
           )

    "Output":

    name Detail (JSONB) State (JSONB)
    apple {"code":"156","color":"red"} [{"ap":"good","op2":"bad"}]
    apple {"code":"156","color":"blue"} [{"ap":"good","op2":"bad"}]
    orange {"code":"156","color":"yellow"} [{"op":"bad","op2":"best"}]
    lemon {"code":"156","color":"red"} [{"cp":"best","op2":"good"}]
    lemon {"code":"156","color":"blue"} [{"cp":"best","op2":"good"}]

    Check the demo here.