How parse multiple parameters in JSON?

user_8118448 0
Viewed 45

I have JSON in a PostgreSQL table as:

{"inputs": [{"desc": "How many people does the video contain?", "name": "Number of People", "type": "dropdown", "values": ["", "Only 1", "2-3", "3+"]}, {"desc": "What is the camera position?", "name": "Movement", "type": "dropdown", "values": ["", "Fixed position", "Moving"]}, {"desc": "From which angle did you shoot the video?", "name": "Shoot Angle", "type": "dropdown", "values": ["", "Frontal recording", "Tight angle: 10-40 degree", "Wide angle: 40-70 degree"]}, {"desc": "From which distance did you shoot the video?", "name": "Distance", "type": "dropdown", "values": ["", "Near/Selfie", "Mid (3-6 ft)", "Far (>6 ft)"]}, {"desc": "What is the video lighting direction?", "name": "Lighting Direction", "type": "dropdown", "values": ["", "Front lit", "Side lit", "Back lit"]}, {"desc": "What is the video background?", "name": "Background", "type": "dropdown", "values": ["", "Outdoors", "In office", "At home", "Plain background"]}, {"desc": "What is the topic in your speech?", "name": "Topic", "type": "dropdown", "values": ["", "Arts and Media", "Business", "Education", "Entertainment", "Food/Eating", "Nutrition", "Healthcare ", "High School Life", "Mental Health", "News", "Technology", "Morals and Ethics", "Phones and Apps", "Sports", "Science"]}]}

This JSON contains multiple "name" keys (and others keys).

How I can parse values for every "name" keys?

I try:

SELECT
        s.projectid,
        s.prompttype,
        s.inputs::jsonb#>>'{inputs,0,desc}' AS desc,
        s.inputs::jsonb#>>'{inputs,0,name}' AS name,
        s.inputs::jsonb#>>'{inputs,0,values}' AS values,
        s.created,
        s.modified
FROM source_redshift.staticprompts AS s;

but I get only one value of JSON.

postgresql sql json
1 Answers

You have to parse the jsonb array as such. With {inputs,0,...} you're retrieving only the first element of the jsonb array. Use jsonb_array_elements instead:

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

Demo: db<>fiddle

user_22753880
Related