PostgreSQL, merge two JSONB objects
-
After deciding to go with my new "JSONB everywhere" strategy, I'm stumbling into my first problems.
I basically have two jsons with generic stats and I want to add them together. In pseudo code, something like:
{"a": 1, "b": 3} + {"b": -1, "c": 2} = {"a": 1, "b": 2, "c": 2}
I thought this would be simple, but no. Here's what I got so far:
SELECT COALESCE(a.key, b.key) AS key, COALESCE(a.value::integer, 0) + COALESCE(b.value::integer, 0) AS value FROM jsonb_each_text('{"a": 1, "b": 3}'::jsonb) a FULL OUTER JOIN jsonb_each_text('{"b": -1, "c": 2}'::jsonb) b ON a.key = b.key
This produces a table like this:
So all I need now is pack this back into the jsonb. Easier said than done. There doesn't seem to be a way to do this with one elegant call.
The closest I got is this:
WITH tabular AS ( -- query from above ) SELECT json_object(array_agg(tabular.key), array_agg(tabular.value)::text[]) FROM tabular
This unfortunately converts numbers to text and there's no way to fix it that I see.
I also got pretty far with this, using the wannabe PIVOT stuff from contrib:
WITH tabular AS ( -- query from above ) SELECT * FROM row_to_json(crosstab('SELECT * FROM tabular', 'SELECT DISTINCT key FROM tabular'))
This unfortunately raises an error - postgres expects me to provide it a list of columns in the resulting record set, which I can't do.
I'll probably end up writing some procedural string builder monstrosity to handle this last part. But I was hoping someone here might have a better idea.
-
But I was hoping someone here might have a better idea.
Don't manipulate JSONB via SQLSeriously though, pull the JSONB out into whatever you're using to call into the DB, and do the processing using a proper JSONB library. Then save the results back into the DB.
Right tool for the right job and all that ;)
-
Nope, I have to do this in database. It needs to be triggered by various stored functions, inside transactions.
-
If this was MSSQL, then I'd suggest a CLR stored procedure. But this is Postgres; no idea whether it has something similar or not.
-
If this was MSSQL, then I'd suggest a CLR stored procedure. But this is Postgres; no idea whether it has something similar or not.
Yup, I can do this in PL/pgSQL. It won't be pretty, but it's doable.
But damn. I'm so close to doing it nicely! Just one more step!
-
My Googling seemed to indicate you can use stuff like Python and C to write procedures; it may be worth looking into the Python possibility (PL/Python)? Or JavaScript (PL/V8), since you're manipulating JSONB?
-
I know you can install additional languages.
I'd rather avoid relying too much on external modules or things you have to separately install, unless absolutely necessary. And so far, it hasn't been.
-
Yup, I can do this in PL/pgSQL. It won't be pretty, but it's doable.
Oh come on, I've seen uglier things than PG/plSQL.
And as @RaceProUK said, you could try Python or something, but I think you can do without and reduce the complexity of installs.
-
That does work in general -
unfortunately, when last I checked, PL/V8 did not yet support JSONBAnd apparently this just changed last month: https://github.com/plv8/plv8/pull/116
So if you have the bleeding edge, you should be all good there.
-
-
The closest I got is this:
WITH tabular AS ( -- query from above ) SELECT json_object(array_agg(tabular.key), array_agg(tabular.value)::
This unfortunately converts numbers to text and there's no way to fix it that I see.textint[]) FROM tabularMaybe? Or am I missing something?
-
Maybe? Or am I missing something?
Signature is
json_object(text[], text[])
, won't work. :-(
-
WITH tabular AS ( SELECT COALESCE(a.key, b.key) AS key, COALESCE(a.value::integer, 0) + COALESCE(b.value::integer, 0) AS value FROM jsonb_each_text('{"a": 1, "b": 3}'::jsonb) a FULL OUTER JOIN jsonb_each_text('{"b": -1, "c": 2}'::jsonb) b ON a.key = b.key ) SELECT ('{' || string_agg('"' || t.key || '": ' || t.value, ',') || '}')::jsonb FROM tabular t
You may call me... maestro.
-
I don't have the time to play with actual code but:
Combined with
crosstab
? Might be cleaner and involve less concatenation?I fully admit I'm asspulling this though.
-
Figured it out: http://what.thedailywtf.com/t/postgresql-merge-two-jsonb-objects/48850/13?u=cartman82
-
Yes, that's what I replied to, actually. But there's concatenation damn it! The elegance is damaged!
-
@RaceProUK Um, ever heard of ACID ?
-
@cartman82 said in PostgreSQL, merge two JSONB objects:
I'd rather avoid relying too much on external modules or things you have to separately install
If I'm reading that page right, there are several that are part of the base distribution. Or perhaps that are just maintained by the PG team (I'm not quite sure). In any case, they're low risk and using a properly plugged in programming language module will keep you within the transactional context and stop Baby Codd Almighty from crying to sleep too much.
-
@Val-Waeselynck said in PostgreSQL, merge two JSONB objects:
Um, ever heard of ACID ?
-
@Val-Waeselynck said in PostgreSQL, merge two JSONB objects:
@RaceProUK Um, ever heard of ACID ?
Have you ever heard of "necroing" (replying to a long forgotten topic) or about RaceProUK's absence from the forum?
-
@JBert said in PostgreSQL, merge two JSONB objects:
Have you ever heard of "necroing" (replying to a long forgotten topic) or about RaceProUK's absence from the forum?
That was, overall, a pretty epic first post on the forum.
-
@Val-Waeselynck said in PostgreSQL, merge two JSONB objects:
@RaceProUK Um, ever heard of ACID ?
100/100 (at the moment - tried it the other day and only got 99): http://acid3.acidtests.org/