Storing foreign keys in postgresql jsonb a bad idea?

Question

What are the problems associated with storing foreign keys in a jsonb column?

Context:

I have a table of items:

Item 
--------------------------
| id   | name | property |
| PK   | text |  jsonb   |

The property column is one-level jsonb of the following structure:

[
  {"value": "white", "item_attribute_id": "1"},
  {"value": "71", "item_attribute_id": "3"},
  {"value": "29", "item_attribute_id": "4"},
  {"value": "48-70", "item_attribute_id": "5"},
  {"value": "190", "item_attribute_id": "6"}
]

The item_attribute_id is a foreign key pointing to a table of attributes, which holds everything related to the given attribute (name, type, description).

I cannot find any literature on why this might be a good/bad practice. Are there any obvious directly related problems that I overlooked?

How would you select the names, types and description for all attributes for an Item? — Kenney, Dec 01 '15 at 17:25
I have tried to find some way, but it seems to me that I would need to leverage my ORM to do so (I use ActiveRecord, which supports the jsonb columns). Did you have something in mind? — jiroch, Dec 01 '15 at 19:23
Indeed - you'd need the ORM and the queries would become more complex than say `SELECT * FROM item_attributes LEFT JOIN attributes USING(attribute_id) WHERE item_id = ?`. Generally, I wouldn't use JSON or any other text/binary based complex content to store information that references the DB itself, but use proper FK's so the db will enforce them. I've had enough trouble with a MysqlDB that has a column storing foreign keys as `\0` separated values... But if it works for you, is well supported, fast, and the queries don't become unnecessarily complex, why not. — Kenney, Dec 01 '15 at 19:46

score 6 · Accepted Answer · edited May 23 '17 at 12:00

You need to take into consideration following things about JSONB type:

queries will be more complex; if you even feel comfortable with all JSONB functions (they are more about SELECT statements), UPDATE operation will still be tricky - please consider @Erwin's answer regarding this.
size overhead; for small docs it will not really matter, but at scale you might hit a wall. Just compare pg_column_size() results for your case.
limited indexing support; you will have no luck when you perform search in array by element (functions like json_array_elements() etc). Default GIN index supports queries with the (CONTAINS) @>, (EXISTS)?, (EXISTS ALL)?& and (EXISTS ANY)?| operators, so you need to be careful with the queries you are using. jsonb supports btree and hash indexes. You can check details here.

Articles to consider:

Thank you very much! I believe I can cope with all of these 1. update will be rare on those columns 2. ok this one i don't know, but i presuppose a few dozen attributes in that jsonb at maximum 3. no arrays should be there — jiroch, Dec 01 '15 at 19:19

Storing foreign keys in postgresql jsonb a bad idea?

1 Answers1