amazon-redshift - Redshift：无法将 JSON 中列表中的多个项目解析为每一行

Question

我在 RedShift 中有一个 JSON。

“值”作为 JSON 文本存储在输入列中。为每个“值”创建唯一记录。查看此表下方的示例：

{"inputs": [{"name": "ambient", "desc": "Select from below the ambient setting that best decribe your environment right now", "values": ["Indoor - Loud", "Indoor - Normal", "Indoor - Whisper", "Outdoor - Loud", "Outdoor - Normal", "Outdoor - Whisper", "Semi-Outdoor - Loud", "Semi-Outdoor - Normal", "Semi-Outdoor - Whisper"]}

因此它必须是这样的： ProjectId : 10. Input value = Indoor – Loud ProjectId : 10. Input value = Indoor – Normal ProjectId : 10. Input value Indoor – Whisper

每个值都需要在 dim_collect_user_inp_configs 表中存储为一行。例如，Indoor-Loud 作为一行，它将有自己的唯一标识符作为 prompt_input_value_id，Indoor-Normal 作为一行，并且在半户外耳语之前它将有自己的唯一标识符作为 prompt_input_value_id。

一个输入列中可能有多个输入“名称”。每个名称及其值都需要单独存储。例子 ：

[{"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"]}] 

我尝试用这个查询来做到这一点：

WITH all_values AS (
        SELECT projectid AS projectid,
               prompttype AS prompttype,
               json_extract_path_text(json_extract_array_element_text(inputs, 0, True), 'name') AS name,
               json_extract_path_text(json_extract_array_element_text(inputs, 0, True), 'desc') AS description,
               json_extract_path_text(json_extract_array_element_text(inputs, 0, True), 'values') AS value,
               scriptid AS scriptid,
               corpuscode AS corpuscode
        FROM source.table
        WHERE
            prompttype = 'input'
        GROUP BY projectid, prompttype, name, description, scriptid, corpuscode, value
        LIMIT 10
       )
SELECT * FROM all_values;

但现在我没有按照我的需要每一行都有“价值”。:(

你能帮助我吗？

谢谢。

标签： amazon-redshift