JSONB Data Type Modification in Postgresql

Question

I have a doubt with modification of jsonb data type in postgres

Basic setup:-

array=> ["1", "2", "3"]
and now I have a postgresql database with an id column and a jsonb datatype column named lets just say cards.

  id    cards
-----+---------
1       {"1": 3, "4": 2}

thats the data in the table named test

Question:

How do I convert the cards of id->1 FROM {"1": 3, "4": 2} TO {"1": 4, "4":2, "2": 1, "3": 1}

How I expect the changes to occur:

From the array, increment by 1 all elements present inside the array that exist in the cards jsonb as a key thus changing {"1": 3} to {"1": 4} and insert the values that don't exist as a key in the cards jsonb with a value of 1 thus changing {"1":4, "4":2} to {"1":4, "4":2, "2":1, "3":1} purely through postgres.

Partial Solution

I asked a senior for support regarding my question and I was told this:-

Roughly (names may differ): object keys to explode cards, array_elements to explode the array, left join them, do the calculation, re-aggregate the object. There may be a more direct way to do this but the above brute-force approach will work.

So I tried to follow through it using these two functions json_each_text(), json_array_elements_text() but ended up stuck halfway into this as well as I was unable to understand what they meant by left joining two columns:-

SELECT jsonb_each_text(tester_cards) AS each_text, jsonb_array_elements_text('[["1", 1], ["2", 1], ["3", 1]]') AS array_elements FROM tester WHERE id=1;

TLDR;

Update statement that checks whether a range of keys from an array exist or not in the jsonb data and automatically increments by 1 or inserts respectively the keys into the jsonb with a value of 1

Now it might look like I'm asking to be spoonfed but I really haven't managed to find anyway to solve it so any assistance would be highly appreciated 🙇

Your senior's right. They meant to start with SELECT * FROM jsonb_each_text(SELECT tester_cards FROM tester WHERE id=1) AS each_text, jsonb_array_elements_text('[["1", 1], ["2", 1], ["3", 1]]') AS array_elements;. Now there's two things that you can LEFT JOIN — Bergi
– Bergi, Commented Jan 23, 2022 at 22:03
Or, since this is for an update statement, you might want to start with a subquery this way: SELECT tester.id, (SELECT * FROM jsonb_each_text(tester.tester_cards) AS each_text, jsonb_array_elements_text('[["1", 1], ["2", 1], ["3", 1]]') AS array_elements) AS result FROM tester; — Bergi
– Bergi, Commented Jan 23, 2022 at 22:06
Do you have an array ["1", "2", "3"] or an array [["1", 1], ["2", 1], ["3", 1]]? — Bergi
– Bergi, Commented Jan 23, 2022 at 22:07
@Bergi the array isn't an issue as its simply input from my side so could be either :D — Intenzi
– Intenzi, Commented Jan 23, 2022 at 22:20
I'm sorry I still didn't understand how LEFT JOIN would work as for the previous part I've already managed to figure, but how am I supposed to progress ahead on it to have an update statement — Intenzi
– Intenzi, Commented Jan 23, 2022 at 22:22

Bergi · Accepted Answer · 2022-01-24 00:07:05Z

1

The key insight is that with jsonb_each and jsonb_object_agg you can round-trip a JSON object in a subquery:

SELECT id, (
  SELECT jsonb_object_agg(key, value)
  FROM jsonb_each(cards)
) AS result
FROM test;

^{(online demo)}

Now you can JOIN these key-value pairs against the jsonb_array_elements of your array input. Your colleague was close, but not quite right: it requires a full outer join, not just a left (or right) join to get all the desired object keys for your output, unless one of your inputs is a subset of the other.

SELECT id, (
  SELECT jsonb_object_agg(COALESCE(obj_key, arr_value), …)
  FROM jsonb_array_elements_text('["1", "2", "3"]') AS arr(arr_value)
  FULL OUTER JOIN jsonb_each(cards) AS obj(obj_key, obj_value) ON obj_key = arr_value
) AS result
FROM test;

^{(online demo)}

Now what's left is only the actual calculation and the conversion to an UPDATE statement:

UPDATE test
SET cards = (
  SELECT jsonb_object_agg(
    COALESCE(key, arr_value),
    COALESCE(obj_value::int, 0) + (arr_value IS NOT NULL)::int
  )
  FROM jsonb_array_elements_text('["1", "2", "3"]') AS arr(arr_value)
  FULL OUTER JOIN jsonb_each_text(cards) AS obj(key, obj_value) ON key = arr_value
);

^{(online demo)}

answered Jan 24, 2022 at 0:07

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Intenzi Over a year ago

That is insightful, I will be sorting through all the information properly. Thankyou for the detailed explanation.

Intenzi Over a year ago

How would it be possible to have a different increment value I thought simply placing more of the same into the array would have that effect for example FROM jsonb_array_elements_text('["1", "2", "2", "5", "5", "5"]') AS arr(arr_value) it didn't have any effect however with same +1 increment only once.

Bergi Over a year ago

I guess for that you would need some sort of GROUP BY and a count(…)? jsonb_object_agg just ignores duplicate keys and keeps the last value. You can see here what the non-aggregated tuples look like. However, instead of duplicating the key values in the array, I'd suggest you just pass {"1": 1, "2": 2, "5": 3} as an object

Collectives™ on Stack Overflow

JSONB Data Type Modification in Postgresql

Basic setup:-

Question:

How I expect the changes to occur:

Partial Solution

TLDR;

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Basic setup:-

Question:

How I expect the changes to occur:

Partial Solution

TLDR;

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related