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屋！