Joining two JSON objects in Snowflake

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.

1. 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.
2. 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

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" }', 'n'))
;

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 } ] }', 'n'))
;

—

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";