How to query nested array with heterogeneous elements in PostgreSQL JSONB column

Question

I have a JSONB field in PostgreSQL (12.5) table Data_Source with the data like that inside:

{
  "C1": [
    {
      "id": 13371,
      "class": "class_A1",
      "inputs": {
        "input_A1": 403096
      },
      "outputs": {
        "output_A1": 403097
      }
    },
    {
      "id": 10200,
      "class": "class_A2",
      "inputs": {
        "input_A2_1": 403096,
        "input_A2_2": 403095
      },
      "outputs": {
        "output_A2": [
          [
            403098,
            {
              "output_A2_1": 403101
            },
            {
              "output_A2_2": [
                403099,
                403100
              ]
            }
          ]
        ],
        "output_A2_3": 403102,
        "output_A2_4": 403103,
        "output_A2_5": 403104
      }
    }
  ]
}

Could you please suggest me some SQL query to extract outputs from the JSONB field. What I need to get as a results:

Output:

name	value
output_A1	403096
output_A2	403098
output_A2_1	403101
output_A2_2	403099
output_A2_2	403100
output_A2_3	403102
output_A2_4	403103
output_A2_5	403104

Any ideas?

Barbaros Özhan · Accepted Answer · 2021-04-13 06:39:48Z

Whenever an array is encountered, then JSONB_ARRAY_ELEMENTS(), or an object is encountered, then JSONB_EACH() functions might be applied, along with auxiliary JSONB_TYPEOF() function to determine respective types, consecutively. At the end, combine the results whether of type array or object or not through use of UNION ALL such as

WITH j AS
(
SELECT j2.*, JSONB_TYPEOF(j2.value) AS type
  FROM t,
  JSONB_EACH(jsdata) AS j0(k,v),
  JSONB_ARRAY_ELEMENTS(v) AS j1,
  JSONB_EACH((j1.value ->> 'outputs')::JSONB) AS j2
), jj AS
(
SELECT key,j1.*,JSONB_TYPEOF(j1.value::JSONB) AS type
  FROM j,
  JSONB_ARRAY_ELEMENTS(value) AS j0(v),
  JSONB_ARRAY_ELEMENTS(v) AS j1
 WHERE type = 'array'  
), jjj AS
(
 SELECT key,j0.v,JSONB_TYPEOF(j0.v::JSONB) AS type,k
   FROM jj,
   JSONB_EACH(value) AS j0(k,v)
  WHERE type IN ('array','object') 
)
SELECT key,value
  FROM
  (
   SELECT key,value,type
     FROM j  
    UNION ALL
   SELECT key,value,type
     FROM jj
    UNION ALL
   SELECT k,v,type
     FROM jjj
   ) jt  
WHERE type NOT IN ('array','object')  
UNION ALL 
SELECT k,value
  FROM jjj,JSONB_ARRAY_ELEMENTS(v) AS j0
 WHERE type IN ('array','object')

Demo

Collectives™ on Stack Overflow

How to query nested array with heterogeneous elements in PostgreSQL JSONB column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related