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?

S-Man · Accepted Answer · 2021-01-27 16:06:43Z

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.

edited Jan 27, 2021 at 16:06

answered Jan 27, 2021 at 7:08

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Minoru Over a year ago

Thanks for the suggestion! This is a work in progress to export data from a content document-based DB to SQL DB and then sync plug to Google BigQuery. So, this SQL DB won't need any update, complex searches or filtering, as I only need to be able to get specific fields based on the ids on BigQuery.

Minoru Over a year ago

I got to reach getting the values, but ideally I still need to merge the values back to have a single id on the table, instead of one row for each value on c column.

Minoru Over a year ago

Got through final step by doing string_agg and group by id.

Collectives™ on Stack Overflow

Join tables by ids in JSON array in PGSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related