在snowflake的object_construct中添加一个硬编码值

Question

我使用Snowflake的Object_Construct来创建一个带有值的JSON数组。

JSON响应中的大多数值都来自表列，但我还需要硬编码一些元数据属性，如language_code到'en'等，这些属性在表中不可用。我尝试了一些选项，如'language_code'作为'en'或值('language_code'，'en')，但它不起作用。

示例代码:

with address as (
SELECT address_id,
array_agg(object_construct(
'addressKey',address_key,
'ChangeTime',CHANGE_TMST,
'addressType',ADDRESS_TYPE,
'ADDRESS_LINE_1',ADDRESS_LINE_1,
'ADDRESS_LINE_2',ADDRESS_LINE_2,
'ADDRESS_LINE_3',ADDRESS_LINE_3,
--'languageCode','en',
))
from ADDRESS
group by address_id) 

Language Code在示例代码中有注释。请建议。

Answer 1

您使用的模式应该可以正常工作:

SELECT address_id,
array_agg(object_construct(
'addressKey',address_key,
...
'languageCode','en'
))
from ADDRESS
group by address_id;

您的错误可能来自周围的查询语法。我注意到您正在定义一个address表，并且还试图从一个名为ADDRESS的表中进行选择:Snowflake将未加引号的标识符视为大写，因此CTE将与表命名相同，这可能会导致问题。您还在参数列表的末尾包含了一个额外的逗号，这将导致语法错误。

这个例子执行得很好:

with ADDRESS as (
select a.* from (values (1, 'one'),(1, 'one-again'), (2, 'two'), (3, 'three')) as a(address_id, address_key)
)
SELECT address_id,
array_agg(object_construct(
'addressKey',address_key,
'languageCode','en'
))
from ADDRESS
group by address_id;

Answer 2

如果我们创建一个小的值表来检查object_construct:

with data(address_id, address_key,CHANGE_TMST, ADDRESS_TYPE, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3)   as (
select * from values
( 1, 'ak1', '2022-06-24 20:18:12'::timestamp, 'type1', 'line1', 'line2', null ),
( 2, 'ak2', '2021-06-24 20:18:12'::timestamp, 'type2', 'line1', 'line2', 'line3' )
)

并使用内部对象代码

select object_construct(
'addressKey',address_key,
'ChangeTime',CHANGE_TMST,
'addressType',ADDRESS_TYPE,
'ADDRESS_LINE_1',ADDRESS_LINE_1,
'ADDRESS_LINE_2',ADDRESS_LINE_2,
'ADDRESS_LINE_3',ADDRESS_LINE_3,
'languageCode','en'
)
from data;

OBJECT_CONSTRUCT('ADDRESSKEY'，ADDRESS_KEY， 'CHANGETIME'，CHANGE_TMST， 'ADDRESSTYPE'，ADDRESS_TYPE， 'ADDRESS_LINE_1'，ADDRESS_LINE_1， 'ADDRESS_LINE_2'，ADDRESS_LINE_2， 'ADDRESS_LINE_3'，ADDRESS_LINE_3， 'LANGUAGECODE'，'EN')
{"ADDRESS_LINE_1";line1";ADDRESS_LINE_2";line2";ChangeTime";;2022-06-24 20:18:12.000" addressKey";;ak1";addressType";;type1";languageCode"; "}
{"ADDRESS_LINE_1";line1";ADDRESS_LINE_2";;line2";;ADDRESS_LINE_3";;line3";;ChangeTime"; ";2021-06-24 20:18:12.000";addressKey";;ak2";;addressType";;;type2";}