PostgreSQL, merge two JSONB objects

cartman82

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.

RaceProUK

@cartman82 said:

But I was hoping someone here might have a better idea.

Don't manipulate JSONB via SQL

Seriously 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 ;)

cartman82

Nope, I have to do this in database. It needs to be triggered by various stored functions, inside transactions.

RaceProUK

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.

cartman82

@RaceProUK said:

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!

RaceProUK

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?

cartman82

I know you can install additional languages.

Feb 13, 2020

Procedural 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.

Onyx

@cartman82 said:

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.

svieira

That does work in general - ~~unfortunately, when last I checked, PL/V8 did not yet support JSONB~~

And 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.

RaceProUK

@svieira said:

PL/V8 did not yet support JSONB

svieira

@cartman82 said:

The closest I got is this:
WITH tabular AS ( -- query from above ) SELECT json_object(array_agg(tabular.key), array_agg(tabular.value)::textint[]) FROM tabular This unfortunately converts numbers to text and there's no way to fix it that I see.

Maybe? Or am I missing something?

cartman82

@svieira said:

Maybe? Or am I missing something?

Signature is json_object(text[], text[]), won't work. :-(

cartman82

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.

Onyx

I don't have the time to play with actual code but:

Nov 9, 2017

JSON Functions

Combined with crosstab? Might be cleaner and involve less concatenation?

I fully admit I'm asspulling this though.

cartman82

Figured it out: http://what.thedailywtf.com/t/postgresql-merge-two-jsonb-objects/48850/13?u=cartman82

Onyx

Yes, that's what I replied to, actually. But there's concatenation damn it! The elegance is damaged!

Val Waeselynck

@RaceProUK Um, ever heard of ACID ?

dkf

@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.