时间戳

首页 > 解决方案 > RedShift：函数 json_extract_array_element_text(super, integer, boolean) 不存在

amazon-redshift - RedShift：函数 json_extract_array_element_text(super, integer, boolean) 不存在

问题描述

我有一个 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 解析“值”列表并为每个列表项创建行。

例子：

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"]

我创建此查询来执行此操作：

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”步骤中），我在最后一步出现错误：

错误：函数 json_extract_array_element_text(super, integer, boolean) 不存在 提示：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。

可能你知道我该如何解决它？

标签： amazon-redshift

解决方案

问题是 values 仍然是 super 类型并且需要是一个字符串。将 super 转换为字符串的函数是 json_serialize()。

我很好奇，所以我根据您的问题构建了一个测试用例。这是我的工作版本：

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;

推荐阅读