Create JSON from a subquery in snowflake

Question

I want to create a JSON string from a list of value, but I've never worked with JSON before. Please see the image below for my 2 tables, and what I want to create on the right.

I tried this, but it doesn't work (excuse my naivety...but I thought this would be the logical implementation of it)

select a.property_key
      ,to_JSON( select application_ID from tableB where a.property_key = b.property_key) as application_list
  from tableA a

I would appreciate the help. I tried googleing, but I find the snowflake documentation very confusing.

Answer 1

Use the window function array_agg to reduce a column to an array.

create table names (
  last_name varchar,
  first_name varchar
);

insert  into names 
    (last_name, first_name) 
values 
    ('waterman', 'tom'), 
    ('waterman', 'alex'),
    ('waterman', 'david'),
    ('barnett', 'rebecca'),
    ('barnett', 'anne');
    
select
    last_name,
    array_agg(first_name) over (partition by last_name) first_names
from names;

The query above produces the following:

LAST_NAME   FIRST_NAMES
waterman    ["tom", "alex", "david" ]
waterman    ["tom", "alex", "david" ]
waterman    ["tom", "alex", "david" ]
barnett     ["rebecca", "anne"  ]
barnett     ["rebecca", "anne"  ]

You can then reduce that to the unique last_name, first_name pairs using the distinct operator.

select
    distinct 
      last_name,
      array_agg(first_name) over (partition by last_name) first_names
from names;

To convert the array of values returned by array_agg to JSON, you can simply cast the result using ::variant.

select
    distinct 
      last_name,
      array_agg(first_name) over (partition by last_name)::variant first_names
from names;

Answer 2

Here's a sample of how to turn rows into individual JSON documents or one JSON array:

-- Get some rows from a sample table
select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;

-- Get each row as its own JSON using object_construct
select object_construct
(
    'NATION', N_NATIONKEY,
    'NAME', N_NAME,
    'REGION_KEY', N_REGIONKEY,
    'COMMENT', N_COMMENT
) as MY_JSON
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION";
                 
-- Get all rows as a JSON array by adding array_agg
select array_agg(object_construct
(
    'NATION', N_NATIONKEY,
    'NAME', N_NAME,
    'REGION_KEY', N_REGIONKEY,
    'COMMENT', N_COMMENT
)) as MY_JSON
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION";   

For either option, keep in mind that JSON is treated as a variant in Snowflake and has a 16mb size limit.

Answer 3

You can do this by using ARRAYAGG function which pivots the values into an array and further converting it into JSON by using TO_JSON function.

select 
  b.property_key PROPERTY_KEY,
  to_json(( arrayagg(b.APPLICATION_ID) within group (order by b.APPLICATION_ID) ) ) APPLICATION_IDS from table_b b, table_a a where b.property_key = a.property_key group by 1;