Postgres SQL add key to all json array

Question

In my database I have table which has a column called items, this column has jsonb type and almost every record has data like this one:

{"items": [{"id": "item-id", "sku": "some-sku", "quantity": 1, "master_sku": "some-master-sku"}]}

I need to create a migration which add type to every item, so it should looks like this:

{"items": [{"id": "item-id", "sku": "some-sku", "quantity": 1, "master_sku": "some-master-sku", "type": "product"}]}

I must add type to every record, and every record looks the same. The problem is, that i have about milion records in my table, and I cannot iterate every item and add new type because it can take too long time and my deploy script may crash.

Guys, how can i do that in the simplest way?

Will it always be "type": "product" or will the value of "type" potentially have some other value? If so, where do you get it from? — 404
– 404, Commented Oct 23, 2018 at 16:23

404 · Accepted Answer · 2018-10-23 16:51:34Z

1

As long as the format/content is always more or less the same, the fastest way to do it would probably be as a string operation, something like this:

UPDATE your_table
SET your_field = REGEXP_REPLACE(your_field::TEXT, '\}(.+?)', ', "type": "product"}\1', 'g')::JSONB
WHERE your_field IS NOT NULL;

Example: https://www.db-fiddle.com/f/fsHoFKz9szpmV5aF4dt7r/1

This just checks for any } character which is followed by something (so we know it's not the final one from the "items" object, and replaces it with the missing key/value (and whatever character was following).

Of course this performs practically no validation, such as whether that key/value already exists. Where you want to be between performance and correctness depends on what you know about your data.

edited Oct 23, 2018 at 16:51

answered Oct 23, 2018 at 16:41

404

8,7522 gold badges33 silver badges53 bronze badges

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

6 Comments

Dawid Sajdak Over a year ago

I can have more than one item in items collection

404 Over a year ago

@DawidSajdak Is that the only variation? Other than that the structure is always {"items":[<zero or more item objects here>]} and nothing further?

404 Over a year ago

@DawidSajdak I've updated it to handle any number of objects in the items array.

Dawid Sajdak Over a year ago

it may works, but it is very dangerous solution, but thanks for helping me ;)

404 Over a year ago

lol true but if you want safety you'll have to use PG's json functions which will be slower. Speed vs safety, your choice.

|

Collectives™ on Stack Overflow

Postgres SQL add key to all json array

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related