PostgreSQL – Adding JSONB Key/Value Pair with UUID

jsonpostgresqluuid

I am trying to add a new key value pair to a jsonb array and I am having some difficulties in doing it.
What I am trying to add is a key id with a uuid value

This is how my data looks right now:

{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

And this is the result I am looking to achieve:

{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "id": "6a1d15ad-468b-4714-81f8-dccffdea3021",
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "id": "8099c2cf-2ff1-4855-9b54-aa0e62d4bfa5",
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

So far I've only managed to add the id key with a static value using this query

select  jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                jsonb '3',
                                true
                            ), imageElements
                    )
            )
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;

My question is, how can I generate a uuid for each key I am trying to add? Instead of my current static value 3 I want to add a uuid that must not be the same for all the elements in the array

Best Answer

I managed to solve this problem using this query:

select to_jsonb(array_agg(jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                to_jsonb(uuid_generate_v4()),
                                true
                            ), imageElements
                    )
            )))
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;

Failed attempt with Exclusion Constraints

I don't believe this is possible to do this with exclusion DDL because I don't believe PostgreSQL supports exclusion constraints on text[]

CREATE TABLE categories(title)
AS VALUES
  ('{"en": "Hello", "ru": "Привет"}'::jsonb);

Helper function

CREATE FUNCTION jsonb_object_keys_as_array(j JSONB)
RETURNS text[]
AS $$
  SELECT ARRAY( SELECT jsonb_object_keys(j) AS d );
$$
LANGUAGE sql
IMMUTABLE;

Exclusion constraint attempt.

ALTER TABLE categories
  ADD EXCLUDE
  USING gist (jsonb_object_keys_as_array(title) WITH &&);

ERROR:  data type text[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I'm asking a more specific question on this right now.

Other solutions

As it pertains to your problem, I believe currently you'll have to resort to a less than optimal solution like,

Implement this with triggers.
Create a function that may or may not use a table or enum to resolve text -> int and then map over text[] returning int[] and use intarray which will permit a GIST index and &&

Or, of course, you can just fix your schema.

Trigger

This method should also be able to make use of a GIST index on catagories(title)

CREATE OR REPLACE FUNCTION resist_duplicate_languages()
RETURNS TRIGGER
AS $$
  DECLARE
    collision bool DEFAULT false;
  BEGIN
    EXECUTE
      FORMAT('SELECT true FROM %I.%I WHERE ctid <> $1 AND title ?| jsonb_object_keys_as_array($2) LIMIT 1', TG_TABLE_SCHEMA, TG_TABLE_NAME )
      INTO collision
      USING NEW.ctid, NEW.title;

    IF collision IS true THEN
      RAISE EXCEPTION 'Can not collide on language %.%(title) languages %', TG_TABLE_SCHEMA, TG_TABLE_NAME, jsonb_object_keys_as_array(NEW.title);
    END IF;

    RETURN null;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER resist_duplicate_languages
  AFTER INSERT OR UPDATE OF title
  ON categories
  FOR EACH ROW
  EXECUTE PROCEDURE resist_duplicate_languages();

Testing

//works
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');

//does not work a second time
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');
ERROR:  Can not collide on language public.categories(title) languages {fr}

PostgreSQL – Update nth Element of Array Using WHERE Clause

How can I select a specific element in "opts" array?

Use the index of the element number as instructed in the manual. For longer paths, the path notation is shorter:

SELECT metadata -> 'options' -> 'opts' -> 0 AS elem0
     , metadata #> '{options, opts, 0}'     AS elem0_path
FROM   product;

Gets the nth element, like the question title asks (JSON array index starts with 0). But your example indicates you actually want the element with "value":"blue". That's not as trivial. You could unnest the nested JSON array with jsonb_array_elements() in a LATERAL join and filter the one you want.

SELECT opt AS elem_blue
--   , metadata #> '{options, opts}' -> (arr.ord::int - 1) AS elem_blue2
FROM   product
     , jsonb_array_elements(metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
WHERE  opt ->> 'value' = 'blue';

Returns nothing if no matching element is found.

WITH ORDNALITY and elem_blue2 are not needed here, but demonstrate a technique we are going to use in the next step.

More explanation:

2.how to update the "qty" (subtract from its current qty) when one or more of them is sold?

Since Postgres 9.5, there is jsonb_set(). To update the value of the qty key of the first element:

UPDATE product
SET    metadata =  jsonb_set(metadata, '{options, opts, 0, qty}', '"29"', false)
WHERE  ... -- some filter

Aside: Any reason your integer numbers in qty are stored as strings ("30"), not as numbers (30)?

To update the "blue" element, we determine the array index with the technique demonstrated above, plus some UPDATE magic to make the it fully dynamic:

UPDATE product p
SET    metadata = jsonb_set(p.metadata, path, qty, false)
FROM   product p1
     , LATERAL ( -- move computations to subquery
   SELECT ARRAY['options', 'opts', (ord - 1)::text, 'qty'] AS path  -- fix off-by-one
        , to_jsonb((opt ->> 'qty')::int - 1)               AS qty   -- subtract here!
   FROM   jsonb_array_elements(p1.metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
   WHERE  opt ->> 'value' = 'blue'
-- AND    ...  -- more filters
-- FOR UPDATE  -- see below
   ) opt
WHERE  p1.product_id = p.product_id  -- use PK for match

The last query writes a number to "qty" ('9'), not a string ('"9"'), assuming you fixed that as commented.

We need to list the table product in the FROM clause another time to allow the LATERAL join (which wouldn't be possible otherwise) - and self-join to it.

Note a tiny race condition. Under heavy concurrent write load, you might want to add a lock clause to the subquery (FOR UPDATE) to prevent other transactions from changing the row between the inner SELECT and the outer UPDATE. See:

Postgres UPDATE ... LIMIT 1

But you shouldn't, really. As you can see, jsonb (or any document type for that matter) is ill-suited for regular updates to single attributes. That's cumbersome and comparatively expensive. A new row with a new version of the complete document has to be written every time. Use a normalized DB design instead, where only a comparatively very small row has to be rewritten and other parts as well as indexes remain untouched.

If only the quantity is updated regularly, you might move that to a 1:n table and merge it into the JSON document in a VIEW or MATERIALIZED VIEW. The redesign is beyond the scope of this question.

Best Answer

Related Solutions

Postgresql – way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

Failed attempt with Exclusion Constraints

Other solutions

Trigger

PostgreSQL – Update nth Element of Array Using WHERE Clause

Related Question