Thanks in advance.

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

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

closed as unclear what you're asking by ypercubeᵀᴹ, Max Vernon, mustaccio, McNets, Colin 't Hart May 25 '17 at 7:34

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
CREATE TABLE table1(id, jsondata)
  VALUES ( 1, $${"foo":"no", "bar":"go"}$$::jsonb );

CREATE TABLE table2(id, foo, bar)
  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.

  • 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.