postgresql - Join tables by ids in JSON array in PGSQL

Question

I have a table (refer to it as A) with 1 column (refer to it as c) that contains a stringified JSON array in the follow format:

[
  {"sys": {"type": "Link", "linkType": "Entry", "id": "27OfJChoPO894W4rA6bQ67"}},
  {"sys": {"type": "Link", "linkType": "Entry", "id": "2ygvvrBSPuWw0uTW4jdDP2"}}
]

Please, note that the array have variable length. The id fields refer to the ID of the second table (B). So, I need to select all fields from A, but populate c with a column from B.

I tried looking for JSON functions to help me get the ids, but I couldn't progress from an array of ids to finally populating it with the column from B. So, my current idea is creating a new table to hold the relation between A and B. What's the best way?

question from:https://stackoverflow.com/questions/65911245/join-tables-by-ids-in-json-array-in-pgsql

Answer 1

demo:db<>fiddle

You can expand your array and use the elements in the JOIN condition

SELECT
    *
FROM
    a,
    json_array_elements(c) as elems
JOIN b ON b.id = elems -> 'sys' ->> 'id'

However, please think about normalizing your data. You shouldn't store JSON data directly if you don't need it, especially arrays are difficult to handle. If you can save the data in to appropriate tables/columns, every single action (update, search, filter and of course join) would be easier and much faster. Furthermore you have the chance for proper indexes.