Joining two JSON objects in Snowflake

Question

What's up,

I have two JSON objects, generated from the same Snowflake table (Table 1 here). I want to join/merge them on their "_id" field, in order to produce this nested json kind of structure.

How can I do this? I tried aliasing them and using SELECT * from dc JOIN rs ON rs.:_id = dc:_id but I'm hitting invalid identifier error, or "unexpected keyword ON" errors.
Is there an easier way to accomplish this merge without doing two separate json OBJECT CONSTRUCT queries?

I've included the JSON samples below

{ "_id": 786433, "rmpostcode": "LL65 1HL" }
{ "_id": 786434, "rmpostcode": "LL65 1HN" }
{ "_id": 786435, "rmpostcode": "LL65 1HP" }
{ "_id": 786436, "rmpostcode": "LL65 1HR" }
{ "_id": 786437, "rmpostcode": "LL65 1HS" }

Generated from a table

SELECT OBJECT_CONSTRUCT(
    '_id', h."ID",
    'rmpostcode', "rmpostcode"
)
FROM TABLE1

And another

{ "_id": 524323, "coords": [ { "eastings": 265099, "northings": 666879 } ] }
{ "_id": 524381, "coords": [ { "eastings": 265787, "northings": 668537 } ] }
{ "_id": 524447, "coords": [ { "eastings": 265024, "northings": 668238 } ] }
{ "_id": 524496, "coords": [ { "eastings": 268534, "northings": 665428 } ] }
{ "_id": 524785, "coords": [ { "eastings": 260938, "northings": 664166 } ] }

Generated using

SELECT OBJECT_CONSTRUCT(
    '_id', h."ID",
    'coords', array_agg(object_construct(
                        'northings', h."northings",
                        'eastings', h."eastings"))
)
FROM TABLE1
group by "ID"

EDIT: Attempting the answer suggested by @Felipe Hoffa still doesn't work. code below:

with dc AS
(
SELECT OBJECT_CONSTRUCT(
    '_id', h."ID",
    'coords', array_agg(object_construct(
                        'northings', h."northings",
                        'eastings', h."eastings"))
)
FROM "V_TABLES_09092020"."DEV"."v31av8oct20hyperoptic" h
group by "ID"
),
rs AS
(SELECT OBJECT_CONSTRUCT(
    '_id', h."ID",
    'rmpostcode', "rmpostcode"
)
FROM "V_TABLES_09092020"."DEV"."v31av8oct20hyperoptic" h
)

SELECT my_object_assign(dc, rs)
FROM dc 
JOIN rs 
ON rs:"_id" = dc:"_id";

gives me a SQL compilation error: error line 23 at position 3 invalid identifier 'RS'

I'm also struggling to create temporary tables in this way:

create or replace temp table dc AS 
SELECT OBJECT_CONSTRUCT(
    '_id', h."ID",
    'coords', array_agg(object_construct(
                        'northings', h."northings",
                        'eastings', h."eastings"))
)
FROM "V_TABLES_09092020"."DEV"."v31av8oct20hyperoptic" h
group by "ID"

but I get

SQL compilation error: Missing column specification

user_132438 · Answer

How to fix the query: You need to add quotes to "_id":

SELECT * 
from dc 
JOIN rs 
ON rs:"_id" = dc:"_id";

Setup:

create or replace temp table dc as
select parse_json(value) dc
from table(split_to_table('{ "_id": 786433, "rmpostcode": "LL65 1HL" }
{ "_id": 786434, "rmpostcode": "LL65 1HN" }
{ "_id": 786435, "rmpostcode": "LL65 1HP" }
{ "_id": 786436, "rmpostcode": "LL65 1HR" }
{ "_id": 786437, "rmpostcode": "LL65 1HS" }', '
'))
;

create or replace temp table rs as
select parse_json(value) rs
from table(split_to_table('{ "_id": 786433, "coords": [ { "eastings": 265099, "northings": 666879 } ] }
{ "_id": 786434, "coords": [ { "eastings": 265787, "northings": 668537 } ] }
{ "_id": 524447, "coords": [ { "eastings": 265024, "northings": 668238 } ] }
{ "_id": 524496, "coords": [ { "eastings": 268534, "northings": 665428 } ] }
{ "_id": 524785, "coords": [ { "eastings": 260938, "northings": 664166 } ] }', '
'))
;

--

Update: If you then want to combine both objects, you could solve this with a simple assign() JS UDF:

create or replace function my_object_assign(o1 VARIANT, o2 VARIANT) 
returns VARIANT 
language javascript 
as 'return Object.assign(O1, O2);';

SELECT my_object_assign(dc, rs)
FROM dc 
JOIN rs 
ON rs:"_id" = dc:"_id";

user_46239 · Answer

OK, based on your comments I think your situation is that you have a table defined similar to this:

create or replace temporary table public.test1 (
  "ID" numeric(38,0),
  "rmpostcode" varchar,
  "eastings" numeric(38,0),
  "northings" numeric(38,0)
);

In this table you have a 1:1 relationship between ID and rmpostcode and each eastings/northings combination. Something like this:

insert into public.test1 ("ID", "rmpostcode", "eastings", "northings")
values  (1111, 'ABC123', 256789, 887345), 
        (2222, 'LH9ZXQ', 678443, 921009),
        (9876, 'PZ12RR', 876234, 237862),
        (4567, 'W113LM', 234233, 123244)
;

To extract this tabular data into a JSON object where the eastings/northings values are in an array, you are correctly trying to use the OBJECT_CONSTRUCT and ARRAY_AGG functions but based on your example code I think your issue is not defining the GROUP BY expression correctly.

This code should produce a JSON object like the one you are describing:

select object_construct(
    '_id', "ID", 
    'rmpostcode', "rmpostcode", 
    'coords', array_agg(object_construct(
                        'northings', "northings",
                        'eastings', "eastings")
                       )
     ) 
from public.test1 
group by "ID", "rmpostcode"

Result:

{    "_id": 9876,    "coords": [      {        "eastings": 876234,        "northings": 237862      }    ],    "rmpostcode": "PZ12RR"  }
{    "_id": 4567,    "coords": [      {        "eastings": 234233,        "northings": 123244      }    ],    "rmpostcode": "W113LM"  }
{    "_id": 1111,    "coords": [      {        "eastings": 256789,        "northings": 887345      }    ],    "rmpostcode": "ABC123"  }
{    "_id": 2222,    "coords": [      {        "eastings": 678443,        "northings": 921009      }    ],    "rmpostcode": "LH9ZXQ"  }

Joining two JSON objects in Snowflake

2 Answers