使用 JSON 字符串格式参数在 Redshift 中创建临时表-解网

问：

我有一个红移存储的 proc 签名，看起来像

CREATE OR REPLACE PROCEDURE get_filtered_results
(
    p_id smallint,
    p_filters varchar(65535), 
    rs_out REFCURSOR
)
LANGUAGE plpgsql

是 JSON 格式的字符串。p_filters

[{idToFilter: 1, range:'2:2', idChildren: 4, description: 'blabla1'}, {idToFilter: 2, range:'3:5', idChildren: 3, description: 'blabla2'},
{idToFilter: 3, range:'2:6', idChildren: 2, description: 'blabla3'}]

我希望能够将该字符串转换为一个临时表，其中每个属性都是一列，数组的每条记录都是我的表中的一行，其查询类似于

    CREATE TEMPORARY TABLE tmp_filter
    AS
    (
        SELECT * FROM JSON_PARSE(p_filters)
    );

有没有简单的方法可以做到这一点？

SQL PostgreSQL Amazon-Redshift PLPGSQL

您是否只是尝试创建一个包含单个超类型值的一列一行的临时表？您存储的 proc 和 CTAS 在语法上看起来都不正确。您能否提供更多有关您希望实现的目标的背景信息？这个字符串（p_filters）从何而来？存储的过程的内容将是 CTAS 吗？将问题分解为几个部分可能会有所帮助 - 1）从字符串创建表，2）使用存储的 proc 做一些事情，3）与解决方案的其他一些方面的接口

0赞 JudgeProphet 11/15/2023

感谢您在我更新问题@BillWeiner发表评论

答：

0赞 Bill Weiner 11/16/2023 #1

因此，正如我之前所说，让我们将其分解成几部分。(

#1 将字符串转换为行和列

这里的第一个问题是您的字符串不是有效的 JSON。JSON 需要双引号，而不是单引号，但这很容易解决。但是，该字符串仍然无效，因为标识符也需要引用。因此，当您有：

[{idToFilter： 1， range：'2：2'， idChildren： 4， description： 'blabla1'}， {idToFilter： 2， range：'3：5'， idChildren： 3， description： 'blabla2'}， {idToFilter： 3， range：'2：6'， idChildren： 2， description： 'blabla3'}]

它确实需要：

[{“idToFilter”： 1， “range”：“2：2”， “idChildren”： 4， “description”： “blabla1”}， {“idToFilter”： 2， “range”：“3：5”， “idChildren”： 3， “description”： “blabla2”}， {“idToFilter”： 3， “range”：“2：6”， “idChildren”： 2， “description”： “blabla3”}]

您需要修复输入字符串（至少是标识符的引用）才能正常工作。

然后我们需要将字符串转换为 super，然后展开它。此 SQL 如下所示：

create table test as select '[{\'idToFilter\': 1, \'range\':\'2:2\', \'idChildren\': 4, \'description\': \'blabla1\'}, {\'idToFilter\': 2, \'range\':\'3:5\', \'idChildren\': 3, \'description\': \'blabla2\'},
{\'idToFilter\': 3, \'range\':\'2:6\', \'idChildren\': 2, \'description\': \'blabla3\'}]' as p_filters; 

SET enable_case_sensitive_identifier TO true;

with fix_quotes as (
select replace(p_filters,'\'','"') as p_filters from test
),
json_value as (
SELECT JSON_PARSE(p_filters) as json from fix_quotes
)
select i."idToFilter", i.range, i."idChildren", i.description 
from json_value j, j.json i;

此示例中的第一个 CTE 是将引号更改为 double。（我手动更改了初始字符串以修复缺少的引号。第二个 CTE 将值转换为 SUPER。顶部选择展开超级。

另请注意，您需要在 Redshift 会话中更改为区分大小写的标识符，因为您的 json 中有大写字符。

从这里开始，需要对其余步骤进行编码，但让我们先看看这是否有意义。这确实是问题的重点。其他步骤是将此查询包装在 CTAS 中，并通过传递输入字符串将其全部放入存储的进程中。这些步骤很简单，但如果需要更多，我可以提供帮助。

上一个：无法从时间戳中删除毫秒

下一个：将不同语言的数据从 Mysql 复制到 Redshift

使用 JSON 字符串格式参数在 Redshift 中创建临时表

Create a temporary table in Redshift using a JSON string format parameter

评论