0

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
askedMar 5, 2022 at 11:07
kmartin's user avatar
1

1 Answer1

0

PostgreSQL has a manyJSON functions. Firstly you must extract keys and values from 'JSONB'. After then you can get the type of values using Postgresjsonb_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 2select *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 fromJSONB 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.

answeredMar 12, 2022 at 14:18
Ramin Faracov's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.