Postgres expand array of object (jsonb)

Question

I have a postgres table in which I want to expand a jsonb column.

The table (called runs) has each participant as a single row, with the jsonb column holding their experiment data.

id |data         |
---|-------------|
id1|[{}, {}]     |
id2|[{}, {}, {}] |

The jsonb column is always an array with an unknown number of objects, where each object has an unknown number of arbitrary keys.

[
  {
    "rt": 3698,
    "phase": "questionnaire",
    "question": 1
  },
  {
    "rt": 3698,
    "phase": "forced-choice",
    "choice": 0,
    "options": ["red", "blue"]
  }
]

I would like to either (1) expand the jsonb column so each object is a row:

id |rt    | phase         | question | choice | options        |
---|------| ------------- | -------- | ------ | -------------- |
id1| 3698 | questionnaire | 1        |        |                |
id1| 5467 | choice        |          | 0      | ["red", "blue] |

OR (2) map the other columns in the row to the jsonb array (here the "id" key):

[
  { 
    "id": "id1",
    "rt": 3698,
    "phase": "questionnaire",
    "question": 1
  },
  {
    "id": "id1",
    "rt": 3698,
    "phase": "forced-choice",
    "choice": 0,
    "options": ["red", "blue"]
  }
]

The fact that the number of objects, the number of keys per object, and the keys themselves are unknown a priori is really stumping me on how to accomplish this. Maybe something like this, but this isn't right...

SELECT id, x.*
FROM
 runs_table, 
 jsonb_populate_recordset(null:runs_table, data) x

Unnest the arrays with `jsonb_array_elements()` and use the function described in [Flatten aggregated key/value pairs from a JSONB field.](https://stackoverflow.com/a/35179515/1995738) — klin, Mar 05 '22 at 11:16

score 0 · Answer 1 · answered Mar 12 '22 at 14:18

PostgreSQL has a many JSON functions. Firstly you must extract keys and values from 'JSONB'. After then you can get the type of values using Postgres jsonb_typeof(jsonb) function. I wrote two samples for you:

-- sample 1 
select * 
from jsonb_array_elements(
    '[
      { 
        "id": "id1",
        "rt": 3698,
        "phase": "questionnaire",
        "question": 1
      },
      {
        "id": "id1",
        "rt": 3698,
        "phase": "forced-choice",
        "choice": 0,
        "options": ["red", "blue"]
      }
    ]'::jsonb 
) t1 (json_data)
cross join jsonb_each(t1.json_data) t2(js_key, js_value)
where jsonb_typeof(t2.js_value::jsonb) = 'array' 

-- sample 2
select *
from jsonb_array_elements(
    '[
      { 
        "id": "id1",
        "rt": 3698,
        "phase": "questionnaire",
        "question": 1
      },
      {
        "id": "id1",
        "rt": 3698,
        "phase": "forced-choice",
        "choice": 0,
        "options": ["red", "blue"]
      }
    ]'::jsonb 
) t1 (json_data)
where jsonb_typeof((t1.json_data->'options')::jsonb) = 'array'

Sample 1: This Query will extract all keys and values from JSONB and after then will be set filtering for showing only array type of values.

Sample 2: Use this query if you know which keys can be array types.

Postgres expand array of object (jsonb)

1 Answers1