Snowflake Json array/square brackets around all objects

I’m transforming Data in a JSON by using that code and get a result, that is pretty okay. But I need it in another format, so that our agency is able to use the data.

This is the Code I use:

SELECT *
 from(
 SELECT ARRAY_CONSTRUCT(
 OBJECT_CONSTRUCT(
'sku',ID,
'price',price,
'bigImageLink', bigImageLink,
'attributes',
  OBJECT_CONSTRUCT(
'make',make,
'model',model,
'submodel',submodel,
'classification',classification,
'year',"year",
'mileage',mileage,
'colour',colour,
'fuel',fuel,
'gearbox',gearbox,
'ps',ps,
'emission-class', emissionclass,
'enginedisplacement',enginedisplacement,
'offertype',offertype) 
))  from
--
(select * from (select 
REPLACE(parse_json(OFFER):"spec":"im:offerID",'"')::varchar AS ID,
.,
.,
.,
'USED' offertype,
REPLACE(parse_json(OFFER):"spec":"price":"consumerPriceGross",'"')::integer AS price,
Split_part(substring(REPLACE(parse_json(OFFER):"spec":"images",'"')::varchar,11),',',0) AS bigimagelink
FROM "DL_Datatap"."PUBLIC"."DT_Garage_garage_vehicle_inventory_raw"
WHERE status='published'
)))

​x
 
SELECT * from( SELECT ARRAY_CONSTRUCT( OBJECT_CONSTRUCT('sku',ID,'price',price,'bigImageLink', bigImageLink,'attributes',  OBJECT_CONSTRUCT('make',make,'model',model,'submodel',submodel,'classification',classification,'year',"year",'mileage',mileage,'colour',colour,'fuel',fuel,'gearbox',gearbox,'ps',ps,'emission-class', emissionclass,'enginedisplacement',enginedisplacement,'offertype',offertype) ))  from--(select * from (select REPLACE(parse_json(OFFER):"spec":"im:offerID",'"')::varchar AS ID,.,.,.,'USED' offertype,REPLACE(parse_json(OFFER):"spec":"price":"consumerPriceGross",'"')::integer AS price,Split_part(substring(REPLACE(parse_json(OFFER):"spec":"images",'"')::varchar,11),',',0) AS bigimagelinkFROM "DL_Datatap"."PUBLIC"."DT_Garage_garage_vehicle_inventory_raw"WHERE status='published')))​

The Result I get is every row as a JSON with squared brackets, which makes absolutely sense:

[{...}]
[{...}]  
[{...}]
[{...}]

 
[{...}][{...}]  [{...}][{...}]​

But what I/our agency needs is every row in {}, limited by comma and all rows enclosed by []:

[
{...},
{...},
{...},
]

 
[{...},{...},{...},]​

Think that’s pretty easy…but I cant find a way to put all rows in another array.

Answer

I’m pretty sure you’ll simply want to use the ARRAY_AGG( ) function to get to what you are looking for, example as follows. Note however, if you are pulling lots of data, you might hit a size limit, and have to think of an alternative:

create table demo_table_1 (province varchar, created_date date);
insert into demo_table_1 (province, created_date) values
    ('Manitoba', '2020-01-18'::date),
    ('Alberta', '2020-01-19'::date);
    
select object_construct(*) as record from demo_table_1;

RECORD
{    "CREATED_DATE": "2020-01-18",    "PROVINCE": "Manitoba"  }
{    "CREATED_DATE": "2020-01-19",    "PROVINCE": "Alberta"  }
    

select array_agg(record) one_record FROM (
  select object_construct(*) as record 
  from demo_table_1);

ONE_RECORD
[{"CREATED_DATE": "2020-01-18", "PROVINCE": "Manitoba"},{"CREATED_DATE": "2020-01-19", "PROVINCE": "Alberta"}]

 
create table demo_table_1 (province varchar, created_date date);insert into demo_table_1 (province, created_date) values    ('Manitoba', '2020-01-18'::date),    ('Alberta', '2020-01-19'::date);    select object_construct(*) as record from demo_table_1;​RECORD{    "CREATED_DATE": "2020-01-18",    "PROVINCE": "Manitoba"  }{    "CREATED_DATE": "2020-01-19",    "PROVINCE": "Alberta"  }    ​select array_agg(record) one_record FROM (  select object_construct(*) as record   from demo_table_1);​ONE_RECORD[{"CREATED_DATE": "2020-01-18", "PROVINCE": "Manitoba"},{"CREATED_DATE": "2020-01-19", "PROVINCE": "Alberta"}]​

Helpful links:

https://docs.snowflake.com/en/sql-reference/functions/object_construct.html

https://docs.snowflake.com/en/sql-reference/functions/array_agg.html

Advertisement

Answer