红移：不支持在引线上取消嵌套子查询的结果

Question

我尝试在RedShift中解析JSON。

我的字符串在列"输入"是:

[{"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中的每个值，名称，desc需要在表中存储为一行"

的例子:id: 1, desc: "视频中有多少人?"id: 2, desc: "相机的位置是什么?"等。

我使用query:

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

得到了一个错误:navigation on column "input "是不允许的，因为它不是SUPER类型

和查询:

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

给了我另一个错误:" FROM中的函数表达式可能不引用相同查询级别的其他关系">

但是当我创建测试JSON为:

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;"从官方RedShift文档-它的工作很好，所有数据从JSON解析到每一行和JSON是正确的。

我如何修复查询工作与我的真实数据?

非常感谢。

Answer 1

我遇到了一个类似的问题。json必须在使用之前在创建的表中解析，不能通过CTE或子查询引用，然后使用。因此，我认为答案是:

CREATE TABLE my_table_with_super 
AS 
(
SELECT c.*, 
JSON_PARSE(c.inputs) AS inputs_json_super 
FROM source.table c
);
SELECT c.*, 
json_row.desc, 
json_row.name, 
json_row.values
FROM my_table_with_super as c,
c.inputs_json_super as json_row;

Answer 2

看起来您混淆了子选择SUPER数据类型的概念。您不能将字符串强制转换为super(使用JSON_PARSE)，然后将其用作同一查询级别的FROM子句的源。我不完全了解你的情况，但我认为这样的事情应该会让你更接近:

SELECT c.*, d.desc, d.name, d.values 
FROM (
SELECT *, JSON_PARSE(inputs) AS inputs_super
FROM source.table 
) AS c, 
JSON_PARSE(c.inputs_super) AS d
;

(这是一个即兴的响应，以显示结构，所以请原谅任何语法问题)

Answer 3

这样更正确:

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                                                      
;