json - 从 Snowflake 卸载数据时将 OBJECT_CONSTRUCT 结果嵌套在 JSON 数组中

Question

我正在使用 OBJECT_CONSTRUCT在具有多行的表上，如下所示:

COPY INTO @%mytable
  FROM (
    SELECT OBJECT_CONSTRUCT(
      'id', id, 'first_name', first_name,
      'last_name', last_name, 'city', city, 'state', state
    ) FROM mytable
  )
  FILE_FORMAT = (TYPE = JSON);

这会产生以下类型的 JSON 输出:

{"city":"Salt Lake City","first_name":"Ryan","id":1,"last_name":"Dalton","state":"UT"}
{"city":"Birmingham","first_name":"Upton","id":2,"last_name":"Conway","state":"AL"}
{"city":"Columbus","first_name":"Kibo","id":3,"last_name":"Horton","state":"GA"}

但是，我想将生成的对象嵌套到 JSON 数组中，如下所示:

[
    {"city":"Salt Lake City","first_name":"Ryan","id":1,"last_name":"Dalton","state":"UT"},
    {"city":"Birmingham","first_name":"Upton","id":2,"last_name":"Conway","state":"AL"},
    {"city":"Columbus","first_name":"Kibo","id":3,"last_name":"Horton","state":"GA"}
]

这可能吗？

Answer 1

我目前无法访问雪花帐户，因此对于未经测试的解决方案，我深表歉意:( 我认为您正在寻找 array_construct，您可以阅读 about here

我希望这样的事情可以工作

COPY INTO @%mytable
  FROM (
    SELECT ARRAY_CONSTRUCT(objs)
    FROM (
       SELECT OBJECT_CONSTRUCT(
         'id', id, 'first_name', first_name,
         'last_name', last_name, 'city', city, 'state', state
       ) as objs FROM mytable
    )
  )
  FILE_FORMAT = (TYPE = JSON);