CODEWITHSUNDEEP
jsonpostgresqlpostgresql-9.6
metalcamp
metalcamp

Reputation: 546

Joining table on JSON object

I have table A which has JSON column f with following content: 

[{"name": "abc", "id": "1"}, {"name": "abcd", "id": "2"}, {"name": "abcde", "id": "3"} ]

I would like to join another table B on id inside JSON object described above but also take property name from JSON object.

I managed to create following query:

WITH sample_data_array(arr) AS (
   SELECT f FROM A
), sample_data_elements(elem) AS (
   SELECT json_array_elements(arr) FROM sample_data_array
)
SELECT CAST(elem->>'id' AS int) AS id, elem->'name' AS name FROM sample_data_elements

which returns following result:

id, name
1, "abc"
2, "abcd"
3, "abcde"

Sample data from table B:

id, title, slug  
1, "title 1", "title-1"
2, "title 2", "title-2"
3, "title 3", "title-3"

How do I join this result with table B and add more data (columns) from said table?

Expected result:

id, name, title, slug
1, "abc", "title 1", "title-1"
2, "abcd", "title 2", "title-2"
3, "abcde", "title 3", "title-3"

Upvotes: 0

Views: 867

Answers (1)

S-Man
S-Man

Reputation: 23676

Click: demo:db<>fiddle

SELECT
    b.id,
    elems ->> 'name' as name,           -- 3
    b.title
FROM
    a,
    json_array_elements(f) as elems     -- 1
JOIN
    b ON b.id = (elems ->> 'id')::int   -- 2
  1. Get the elements out of the json array with json_array_elements()
  2. Now you can join on the JSON attributes (in this case: The the id attribut as text and cast it into an int value)
  3. After joining take all relevant values from b and the array elements (like the name)

Upvotes: 1

Related Questions