RedShift:函数 json_extract_array_element_text(super, integer, boolean) 不存在 [英] RedShift: function json_extract_array_element_text(super, integer, boolean) does not exist
问题描述
我有一个数据到 RedShift:
I have a data to RedShift:
id: 210396
created: 2021-09-01 05:42:15.80726
inputs_super: [{"desc":" Please check the pledge box, Pledge content","name":"pledge","type":"dropdown","values":["Agree","Disagree"]}]
desc: " Please check the pledge box, Pledge content"
name: "pledge"
values: ["Agree","Disagree"]
我需要解析值"列表在 RedShift 并为每个列表项创建行.
I need to parse list of "values" at RedShift and create row for each of list's items.
示例:
id: 210396
created: 2021-09-01 05:42:15.80726
inputs_super: [{"desc":" Please check the pledge box, Pledge content","name":"pledge","type":"dropdown","values":["Agree","Disagree"]}]
desc: " Please check the pledge box, Pledge content"
name: "pledge"
values: ["Agree"]
id: 210396
created: 2021-09-01 05:42:15.80726
inputs_super: [{"desc":" Please check the pledge box, Pledge content","name":"pledge","type":"dropdown","values":["Agree","Disagree"]}]
desc: " Please check the pledge box, Pledge content"
name: "pledge"
values: ["Disagree"]
我创建此查询来执行此操作:
I create this query to do this operation:
CREATE TEMP TABLE seq_0_to_100 AS (
SELECT 0 AS i UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
-- I am stopping here, you could easily generate this as a VIEW with 100+ real rows...
);
WITH all_values AS (
SELECT c.*, d.desc, d.name, d.values
FROM (
SELECT id, created, JSON_PARSE(inputs) AS inputs_super
FROM course.table
WHERE prompttype = 'input'
) AS c,
c.inputs_super AS d
ORDER BY created DESC
LIMIT 10
), split_values AS (
SELECT id, json_extract_array_element_text(values, seq.i, True) AS size
FROM all_values, seq_0_to_100 AS seq
WHERE seq.i < JSON_ARRAY_LENGTH(values)
)
SELECT * FROM split_values;
但是在尝试拆分列表(在split_values"步骤中)的最后一步时出现错误:
But I got an error on the last step when try to split list (on "split_values" step):
错误:函数 json_extract_array_element_text(super, integer, boolean) 不存在提示:没有函数匹配给定的名称和参数类型.您可能需要添加显式类型转换.
ERROR: function json_extract_array_element_text(super, integer, boolean) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.
你知道我该如何解决吗?
May be you know how I can fix it?
推荐答案
问题是 values 仍然是 super 类型,需要是字符串.将 super 转换为字符串的函数是 json_serialize().
The issue is that values is still of type super and needs to be a string. The function to convert super to string is json_serialize().
我很好奇,所以我根据您的问题构建了一个测试用例.这是我的工作版本:
I was curious so I built a test case from your question. Here's my working version:
drop table super;
create table super as select 210396 as id, '2021-09-01 05:42:15.80726'::timestamp as created,
'[{"desc":" Please check the pledge box, Pledge content","name":"pledge","type":"dropdown","values":["Agree","Disagree"]}]'::text as inputs,
'Please check the pledge box, Pledge content' as desc, 'pledge' as name;
drop table seq_0_to_100;
CREATE TEMP TABLE seq_0_to_100 AS (
SELECT 0 AS i UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
-- I am stopping here, you could easily generate this as a VIEW with 100+ real rows...
);
WITH all_values AS (
SELECT c.*, d.desc, d.name, d.values
FROM (
SELECT id, created, JSON_PARSE(inputs) AS inputs_super
FROM super
--WHERE prompttype = 'input'
) AS c,
c.inputs_super AS d
ORDER BY created DESC
--LIMIT 10
), split_values AS (
SELECT id, i, json_serialize(values), json_extract_array_element_text(json_serialize(values), seq.i) AS size
FROM all_values, seq_0_to_100 AS seq
WHERE seq.i < JSON_ARRAY_LENGTH(json_serialize(values))
)
SELECT * FROM split_values;
这篇关于RedShift:函数 json_extract_array_element_text(super, integer, boolean) 不存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!