Postgres SQL add key to all json array

Question 1

In my database I have table which has a column calleditems, this column hasjsonb 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 addtype 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 addtype 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 newtype because it can take too long time and my deploy script may crash.

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

Question 2

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?

Question 3

@eurotrash it will be alwaysproduct

Question 4

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_tableSET your_field = REGEXP_REPLACE(your_field::TEXT, '\}(.+?)', ', "type": "product"}\1', 'g')::JSONBWHERE 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.

Question 5

I can have more than one item in items collection

Question 6

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

Question 7

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

Question 8

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

Question 9

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.

404 8,7622 gold badges33 silver badges53 bronze badges · Accepted Answer · 2018-10-23 16:51:34Z

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_tableSET your_field = REGEXP_REPLACE(your_field::TEXT, '\}(.+?)', ', "type": "product"}\1', 'g')::JSONBWHERE 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.

@DawidSajdak Is that the only variation? Other than that the structure is always{"items":[<zero or more item objects here>]} and nothing further?
@DawidSajdak I've updated it to handle any number of objects in the items array.
it may works, but it is very dangerous solution, but thanks for helping me ;)
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.

Movatterモバイル変換

Collectives™ on Stack Overflow

Postgres SQL add key to all json array

1 Answer1

6 Comments

Your Answer

Sign up orlog in

Post as a guest

Related

Hot Network Questions

Subscribe to RSS