Postgres JSONB Field Manipulations
Since it was announced, the JSONB field in Postgres has been one of the most useful fields I've encountered. It's the ability to have complex data as a single field, with very powerful querying tools into the data - as needed. But one thing that has always been a little tough for me is the manipulation of the data, in SQL, so that it stays as JSON, but it's been altered from it's as-stored value.
Let's say we have a table that has a JSONB field that has an array of Objects, each looking something like this:
{ "id": 101, "size": "large", "score": 91, "count": 1232 }
and there can be more, but what you really want in your SELECT is just an Array of the id and score, and nothing else. It still needs to be an Array, and each element needs to be an Object, but a smaller Object, with just those two attributes.
From the docs, Postgres has jsonb_array_elements() that can turn an Array into something that looks like a table... and we can use the ->> notation to get at the value of an attribute, but how does it all fit together to disassemble, modify, and re-assemble all the elements? A sub-select.
Let's walk through one.
SELECT jsonb_agg( jsonb_build_object( 'id', t.value->>'id', 'score', t.value->>'score' ) ) FROM jsonb_array_elements(c.targets) t
the json_build_object() will take the key/value pairs it's given, and create a new Object. The source data for this is t and is the output of the jsonb_array_elements() function in the targets field, which is the JSONB file that is holding this data.
Then to pull this together, you might have a query like:
SELECT c.id, c.name, (SELECT jsonb_agg(jsonb_build_object( 'id', t.value->>'id', 'score', t.value->>'score')) FROM jsonb_array_elements(c.targets) t WHERE t.id = c.id) AS targets FROM company c WHERE ...
It's not easy... but it's powerful, and there are a lot of things that can be done with this kind of manipulation... it's just going to take a little practice. 🙂