PostgreSQL: parse multiple values to each row

Question

I have this JSON in PostgreSQL table:

[{"desc": "Select the setting that you will do the recordings under.", "name": "ambient", "type": "dropdown", "values": ["", "Indoors + High + Loud", "Indoors + High + Normal", "Indoors + Low + Normal", "Indoors + Low + LowVolume", "Outdoors + High + Normal", "Outdoors + Low + Loud", "Outdoors + Low + Normal", "Outdoors + Low + LowVolume"]}, {"desc": "Select the noise type that you will do the recordings under.", "name": "Noise type", "type": "dropdown", "values": ["", "Human Speech", "Ambient Speech", "Non-Speech"]}, {"desc": "What is the most accurate description of where you are recording? (O: for Outdoors, I: for Indoors)", "name": "surroundings", "type": "dropdown", "values": ["", "O: Backyards/Park with people talking/music", "O: Busy intersection/Transportation station/Construction", "O: Walking/Cycling in noisy streets", "O: Windy outside (Beach, Deck etc.)", "O: Car with Engine on (Window open in streets)", "O: In backyard/a park/streets alone/with not much noise", "O: Other", "I: Home with TV/noisy appliance on", "I: Home with people talking/kids", "I: Inside noisy public buildings/places", "I: Home with not much noise", "I: Other"]}, {"desc": "If you selected Other, please describe your surroundings below. (Skip if not)", "name": "other_surroundings", "type": "string"}]

Value "name" contains from list, example ["", "Indoors + High + Loud", "Indoors + High + Normal", "Indoors + Low + Normal", "Indoors + Low + LowVolume", "Outdoors + High + Normal", "Outdoors + Low + Loud", "Outdoors + Low + Normal", "Outdoors + Low + LowVolume"]

I need to create rows in another table for each item in this list

["", "Indoors + High + Loud", "Indoors + High + Normal", "Indoors + Low + Normal", "Indoors + Low + LowVolume", "Outdoors + High + Normal", "Outdoors + Low + Loud", "Outdoors + Low + Normal", "Outdoors + Low + LowVolume"]

Example,

""
"Indoors + High + Loud" etc.

I try to use this query:

-- For multiple choice from JSON
SELECT
  s.projectid,
  s.prompttype,
  el.inputs->>'name' AS name,
  el.inputs->>'desc' AS desc,
  el.inputs->>'values' AS values,
  s.created,
  s.modified
FROM source_redshift.staticprompts AS s,
     jsonb_array_elements(s.inputs::jsonb) el(inputs);

But it parses the list from JSON only. Not every item from key "value".

Do you want parse the string inside "values" attribute?

Sergey Zaykov
– Sergey Zaykov

2021-08-25 11:24:40 +00:00
Commented Aug 25, 2021 at 11:24 — Sergey Zaykov
– Sergey Zaykov, Commented Aug 25, 2021 at 11:24

Jim Jones · Accepted Answer · 2021-08-25 11:36:13Z

1

Use nested jsonb_array_elements,e.g.:

SELECT 
  jsonb_array_elements(
    jsonb_array_elements(s.inputs->'inputs')->'values')
FROM staticprompts AS s

Your query should look like this:

SELECT
  s.projectid,
  s.prompttype,
  el.inputs->>'name' AS name,
  el.inputs->>'desc' AS desc,
  el.inputs->>'values' AS values,
  jsonb_array_elements(el.inputs->'values'),
  s.created,
  s.modified
FROM staticprompts AS s,
     jsonb_array_elements(s.inputs::jsonb->'inputs') el(inputs);

answered Aug 25, 2021 at 11:36

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: parse multiple values to each row

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