-2

Thanks in advance.

table-1
| ID | name | json |
| 1  | jack | {color: 'red', size: 'large' } |


table-2
| ID | color | size | qty |
| 1  | black | small|  10 |

If json is not null result

| ID | name | color | size  | qty |
| 1  | jack | black | small | 10 |

If json is null

| ID | name | qty |
| 1  | jack | 10  |

Table 1 will join Table 2. If json is not null need to select all keys from json as columns on table 2 otherwise if json is null we don't select them. Just can't quite figure out the json part.

Any help would be greatly appreciated.

  • 1
    Please, add a minimum table schema, and the desired result. – McNets May 24 '17 at 19:59
  • 1
    I've updated to include the schema, but we do not know what you're talking about still @keither78. Do you mean to type the columns of table2, or do you want that automatic (which would require dynamic sql), and be substantially harder..? – Evan Carroll May 24 '17 at 20:18
  • I edited it to hopefully clear it up a bit? haha. Thank y'all for the quick responses. – keither78 May 24 '17 at 20:24
  • How do you know what keys to not-select if JSON is null. Look at your example. – Evan Carroll May 24 '17 at 20:29
1
CREATE TABLE table1(id, jsondata)
AS
  VALUES ( 1, $${"foo":"no", "bar":"go"}$$::jsonb );

CREATE TABLE table2(id, foo, bar)
AS
  VALUES ( 1, 'yes', 'no' );

Just use coalesce

SELECT t1.id,
  coalesce(jsondata->>'foo', t2.foo) AS foo,
  coalesce(jsondata->>'bar', t2.bar) AS bar
FROM table1 AS t1
JOIN table2 AS t2
  USING (id);

Also, when someone updates your question to CTAS and schema, you should keep it and not go back to a textual grid.

| improve this answer | |
  • 1
    i didn't see it.. sorry about that – keither78 May 24 '17 at 20:26
  • I disagree: the "textual grid" is a much better representation of sample data. A CTAS or plain insert statements might be supplied additionally. – a_horse_with_no_name May 24 '17 at 20:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.