CODEWITHSUNDEEP
postgresqljsonb
Intenzi
Intenzi

Reputation: 17

JSONB Data Type Modification in Postgresql

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.

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;

Showcase of Code

TLDR;

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 🙇

Upvotes: 0

Views: 245

Answers (1)

Bergi
Bergi

Reputation: 665555

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)

Upvotes: 1

Related Questions