2

I have data that looks like inside a json table in postgres with a column called content has data similar to 

{schd:[{key1:val1, key2:val2}]}

The way I extracted the key was 

jsonb_array_elements('content'->'schd')->'key1'

However I cannot use this to join/on as I receive the error 

ERROR:  argument of JOIN/ON must not return a set

I tried using LATERAL but I need clarification on whether this creates a cross join because the output does not look quiet right.

Are there any suggestions on other methods I should employ here.

user2510479
  • 1,528
  • 13
  • 17
  • 1
    Your JSON contains an array. If you (LATERAL) JOIN it, then it *may* duplicate your rows, because arrays can have multiple elements. It's a fact that you can't get around. If that creates unwanted duplication than it's sign, that maybe you don't want an array there at all. – pozs Mar 14 '17 at 09:36
  • *I tried using LATERAL but I need clarification on whether this creates a cross join* -- If you specifiy `CROSS JOIN LATERAL`, then it will create one (or `table1, LATERAL ...` that is a `CROSS JOIN` too) – pozs Mar 14 '17 at 09:38

1 Answers1

0

{"Schd":["key1":"val1", "key2":"val2"]} is not a valid Json because the keys in the array are not numeric.

If the array is in fact an object like {"Schd":{"key1":"val1", "val1":"val2"}} you can access val1 with:

test=# SELECT '{"Schd":{"key1":"val1", "val1":"val2"}}'::json->'Schd'->'key1';
 ?column? 
----------
 "val1"
(1 row)

If its an array like {"Schd":["val1", "val2"]} you can access val1 with:

test=# SELECT '{"Schd":["val1", "val2"]}'::json->'Schd'->0;
 ?column? 
----------
 "val1"
(1 row)

Update:

If the Json is {"schd":[{"key1":"val1", "key2":"val2"}]}, here is how to get val1:

test=# SELECT '{"schd":[{"key1":"val1", "key2":"val2"}]}'::json->'schd'->0->'key1';
 ?column? 
----------
 "val1"
(1 row)
Gab
  • 3,404
  • 1
  • 11
  • 22