主页 / 问题 / 1327178
Accepted
Viktor Andriichuk
Asked:2022-09-10 18:19:20 +0000 UTC2022-09-10 18:19:20 +0000 UTC

RedShift：不能取消嵌套 JSON

  • 772

大家好。

我需要在 RedShift 中解析 JSON。

要解析的字符串位于“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"]}]

我需要以一种可以提取每个唯一值并与它们形成单独行的方式解析 JSON。

例如：

id: 1, desc: "How many people does the video contain?" 
id: 2, desc: "What is the camera position?"
etc.

我尝试查询：

SELECT c.*, d.desc, d.name, d.values FROM source.table AS c, c.inputs AS d;

我收到一个错误：不允许在“输入”列上导航，因为它不是超级类型

我在官方 RedShift 文档中阅读了此查询：https ://docs.aws.amazon.com/redshift/latest/dg/query-super.html

但是根据我目前的数据，它不能正常工作。

当我尝试应用他们文档中描述的内容并在那里添加我的数据中的一行时

CREATE TABLE test_parse_json_super
(
  id smallint,
  details super
);
 
INSERT INTO test_parse_json_super VALUES(1, JSON_PARSE('[{"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"]}]'));

并使用文档中的查询"SELECT c.*, d.desc, d.name, d.values FROM test_parse_json_super AS c, c.details AS d;"

然后它工作并且所有数据正常分解成行（以及JSON本身是正确的）。

我不明白我需要做什么才能使查询对真实表中的真实数据而不是测试表起作用？

也许有人会告诉？

提前致谢！

postgresql

1 个回答

  1. Best Answer

    是这样的吗：

    SELECT c.*, d.desc, d.name, d.values 
FROM (
    SELECT id, created, JSON_PARSE(inputs) AS inputs_super
    FROM source.table
    WHERE prompttype = 'input'
    ) AS c, 
c.inputs_super AS d
    • 0

相关问题