acidzombie24 acidzombie24 - 1 year ago 75
JSON Question

How do I implement a SET in postgresql?

In postgresql 9.5 docs it says

JSON objects are better suited than arrays for testing containment or existence when there are many keys or elements involved, because unlike arrays they are internally optimized for searching, and do not need to be searched linearly.

I haven't used json or jsonb in postgresql. I was planning to use
to implement a set by checking if the value is in the list using ANY. Now I'm considering using jsonb. Is it possible to add a value into the array or is that likely going to be an expensive operation? How do I add an element? (
select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb)
appears to be an error) The suspected size will be between 500-10K of ints (4bytes).

Answer Source

The docs imply that you could store a set like 21412,32356,754432 as the following jsonb object:

{"21412": null, "32356": null, "754432": null}

Notice that we've turned the integers into keys in the objects which have to be strings. So if you store object like the above in a column s of type jsonb in a table t, then you could search for all rows that contain a value in the set as follows:

SELECT * FROM t WHERE s ? 3523::text;

An element can be added as follows:

SELECT jsonb_set(s, array[35232::text], 'null'::jsonb)
WHERE .......;

Note though, that the above is probably a bad idea. This is not how you are supposed to store a set in a relational database. For example, consider this: every time you add an element to the set (be it a jsonb, integer[], or whatever), the entire row has to be rewritten. This will take Θ(n) time on top of the time the insertion will take. Another issue is that it will be harder to do joins involving values from the set column. So the correct way to implement a set is to create an extra table and add one row for each set element, for each set.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download