PostgreSQL: Join table on JSON field id inside array

Vote count:

0

Suppose you have the following tables, say

table: content_type

id serial not null tabs json

table: data_type

id serial not null html text

This is just a tiny example to illustrate.

The json stored in tabs could be like eg:

[{"name": "tab1","properties": [{"name": "prop1","order": 1,"help_text": "help text","description": "description","data_type": 1}]},{"name": "tab2","properties": [{"name": "prop2","order": 1,"help_text": "help text2","description": "description2","data_type": 1},{"name": "prop3","order": 2,"help_text": "help text3","description": "description3","data_type": 1}]}]

What I'm looking to achieve now is to make a join like the following pseudo code:

select content_type.id, content_type.tabs, data_type.id, data_type.html from content_type join data_type on data_type.id = content_type.tabs::json->'data_type'::int

Basically I'm looking for the same result as one would have if it was 2 or more tables joined together on column id's, except that one of the "tables" in this case is located inside an array of json objects.

Hope you can help me with this, because I seem to be somewhat stuck.

Ps. Using the latest 9.4beta3 btw.

asked 1 min ago

SK84

11

PostgreSQL: Join table on JSON field id inside array