Add new fields to nested JSON array in JSONB

Question

I have a nested JSON structure stored in a PostgreSQL table.

Table users:

id | content [JSON]

JSON:

{
  "purchases": [
    {
      "id": 1,
      "costs": [
        {
          "amount": 23
        },
        {
          "amount": 34
        }
      ]
    },
    {
      "id": 2,
      "costs": [
        {
          "amount": 42
        }
      ]
    }
  ]
}

I would like to add a field "jsonClass": "Static" to all the objects within the costs array so I have following in the end:

{
  "purchases": [
    {
      "id": 1,
      "costs": [
        {
          "jsonClass": "Static",
          "amount": 23
        },
        {
          "jsonClass": "Static",
          "amount": 34
        }
      ]
    },
    {
      "id": 2,
      "costs": [
        {
          "jsonClass": "Static",
          "amount": 42
        }
      ]
    }
  ]
}

I couldn't figure out how to add values to such a nested structure. Anyone knows how to achieve such thing? The only way I found was to make it a text and do string replace which is not very performant and I have a lot of such entries.

This would be soooo easy with a properly normalized data model. blog.2ndquadrant.com/… — user330315
– user330315, Commented Sep 5, 2018 at 7:07
@a_horse_with_no_name Yes, I know, but the given data is not normalized. It's a PSQL DB used as a document store :/ — messy
– messy, Commented Sep 5, 2018 at 7:16

Amadan · Accepted Answer · 2018-09-05 08:57:23Z

2

Unfortunately, due to having to change multiple sub-objects, I don't know of a better way than to deconstruct and then reconstruct the object. It gets pretty hairy.

UPDATE users
SET content=(
  SELECT jsonb_agg(purchase)
  FROM (
    SELECT jsonb_build_object('id', pid, 'purchases', jsonb_agg(cost)) AS purchase
    FROM (
      SELECT pid, cost || '{"jsonClass":"static"}'::jsonb AS cost
      FROM (
        SELECT purchase->'id' AS pid, jsonb_array_elements(purchase->'costs') AS cost 
        FROM jsonb_array_elements(content::jsonb->'purchases') AS purchase
      ) AS Q  
    ) AS R
    GROUP BY pid
  ) AS S
);

Fiddle

EDIT: Sorry about all the edits, forgot to test for multiple rows. Should be good now. It might be possible to simplify it a bit more, not sure.

edited Sep 5, 2018 at 8:57

answered Sep 5, 2018 at 7:50

Amadan

200k23 gold badges252 silver badges321 bronze badges

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

2 Comments

messy Over a year ago

It does its job which is super nice, but it's still takes too much time to apply these changes in one query. I'll do it in chunks.

Amadan Over a year ago

Unfortunately, yeah, JSON manipulation is not fast. I don't think I can write it any faster (though it might be possible to be more compact). I refer you to @a_horse_with_no_name above :P

Collectives™ on Stack Overflow

Add new fields to nested JSON array in JSONB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related