title: Joining two JSON objects in Snowflake
body: <p>What's up,</p>
  <p>I have two JSON objects, generated from the same Snowflake table (Table 1 here).
  I want to join/merge them on their &quot;_id&quot; field, in order to produce this nested json kind of structure.</p>
  <ol>
  <li>How can I do this? I tried aliasing them and using <code>SELECT * from dc JOIN rs ON rs.:_id = dc:_id</code> but I'm hitting invalid identifier error, or &quot;unexpected keyword ON&quot; errors.</li>
  <li>Is there an easier way to accomplish this merge without doing two separate json OBJECT CONSTRUCT queries?</li>
  </ol>
  <p>I've included the JSON samples below</p>
  <pre><code>{ &quot;_id&quot;: 786433, &quot;rmpostcode&quot;: &quot;LL65 1HL&quot; }
  { &quot;_id&quot;: 786434, &quot;rmpostcode&quot;: &quot;LL65 1HN&quot; }
  { &quot;_id&quot;: 786435, &quot;rmpostcode&quot;: &quot;LL65 1HP&quot; }
  { &quot;_id&quot;: 786436, &quot;rmpostcode&quot;: &quot;LL65 1HR&quot; }
  { &quot;_id&quot;: 786437, &quot;rmpostcode&quot;: &quot;LL65 1HS&quot; }
  </code></pre>
  <p>Generated from a table</p>
  <pre><code>SELECT OBJECT_CONSTRUCT(
      '_id', h.&quot;ID&quot;,
      'rmpostcode', &quot;rmpostcode&quot;
  )
  FROM TABLE1
  </code></pre>
  <p>And another</p>
  <pre><code>{ &quot;_id&quot;: 524323, &quot;coords&quot;: [ { &quot;eastings&quot;: 265099, &quot;northings&quot;: 666879 } ] }
  { &quot;_id&quot;: 524381, &quot;coords&quot;: [ { &quot;eastings&quot;: 265787, &quot;northings&quot;: 668537 } ] }
  { &quot;_id&quot;: 524447, &quot;coords&quot;: [ { &quot;eastings&quot;: 265024, &quot;northings&quot;: 668238 } ] }
  { &quot;_id&quot;: 524496, &quot;coords&quot;: [ { &quot;eastings&quot;: 268534, &quot;northings&quot;: 665428 } ] }
  { &quot;_id&quot;: 524785, &quot;coords&quot;: [ { &quot;eastings&quot;: 260938, &quot;northings&quot;: 664166 } ] }
  </code></pre>
  <p>Generated using</p>
  <pre><code>SELECT OBJECT_CONSTRUCT(
      '_id', h.&quot;ID&quot;,
      'coords', array_agg(object_construct(
                          'northings', h.&quot;northings&quot;,
                          'eastings', h.&quot;eastings&quot;))
  )
  FROM TABLE1
  group by &quot;ID&quot;
  </code></pre>
  <hr />
  <p>EDIT: Attempting the answer suggested by @Felipe Hoffa still doesn't work. code below:</p>
  <pre><code>with dc AS
  (
  SELECT OBJECT_CONSTRUCT(
      '_id', h.&quot;ID&quot;,
      'coords', array_agg(object_construct(
                          'northings', h.&quot;northings&quot;,
                          'eastings', h.&quot;eastings&quot;))
  )
  FROM &quot;V_TABLES_09092020&quot;.&quot;DEV&quot;.&quot;v31av8oct20hyperoptic&quot; h
  group by &quot;ID&quot;
  ),
  rs AS
  (SELECT OBJECT_CONSTRUCT(
      '_id', h.&quot;ID&quot;,
      'rmpostcode', &quot;rmpostcode&quot;
  )
  FROM &quot;V_TABLES_09092020&quot;.&quot;DEV&quot;.&quot;v31av8oct20hyperoptic&quot; h
  )
  
  SELECT my_object_assign(dc, rs)
  FROM dc 
  JOIN rs 
  ON rs:&quot;_id&quot; = dc:&quot;_id&quot;;
  </code></pre>
  <p>gives me a <code>SQL compilation error: error line 23 at position 3 invalid identifier 'RS'</code></p>
  <p>I'm also struggling to create temporary tables in this way:</p>
  <pre><code>create or replace temp table dc AS 
  SELECT OBJECT_CONSTRUCT(
      '_id', h.&quot;ID&quot;,
      'coords', array_agg(object_construct(
                          'northings', h.&quot;northings&quot;,
                          'eastings', h.&quot;eastings&quot;))
  )
  FROM &quot;V_TABLES_09092020&quot;.&quot;DEV&quot;.&quot;v31av8oct20hyperoptic&quot; h
  group by &quot;ID&quot;
  </code></pre>
  <p>but I get</p>
  <pre><code>SQL compilation error: Missing column specification
  </code></pre>