Update specific object in array of objects Postgres jsonb

2
votes

I am attempting to update a jsonb column pagesRead on table Books which contains an array of objects. The structure of it looks similar to this:

[{
    "book": "Moby Dick",
    "pagesRead": [
    "1",
    "2",
    "3",
    "4"
    ]
},
{
    "book": "Book Thief",
    "pagesRead": [
    "1",
    "2"
    ]
}]

What I am trying to do is update the pagesRead when a specific page of the book is read or if someone has started a new book, add an extra entry into it.

I am able to retrieve the specific book details, but I am unsure about how to update it.

EDIT: So I had to use the Update query from S-Man to add a book entry, but I used the Insert query from Barbaros Özhan to handle updating the page

2
sqljsonpostgresqljsonb

2 Answers

3
votes

Some thoughts before:

  1. You should never store structured data as it is in one column. This yields problems with updates, indexing (so, searching/performance), filtering, everything. Please normalize everything into proper tables and columns
  2. You should never store arrays. Normalize it.
  3. Do not use type text to store integer (pages)
  4. "pagesRead" is a sibling of your filter element ("book"). This makes it much more complicated to reference it than referencing it as a child. So think about the book name (or better: an id) as key like {"my_id_for_book_thief": {"name" : "Book Thief", "pagesRead": [...]}}. In that case, you could use a path for referencing it. Otherwise, we need to extract the array, have a look into each book attribute and reference its sibling

demo:db<>fiddle

Adding a book is quite simple (Assuming that you are using type jsonb instead of type json):

SELECT mydata || '{"book": "Lord Of The Rings", "pagesRead": []}'
FROM mytable

Update:

UPDATE mytable
SET mycolumn = mycolumn || '{"book": "Lord Of The Rings", "pagesRead": []}'

Adding a pagesRead value:

SELECT 
    jsonb_agg(                                                         -- 4
        jsonb_build_object(                                            -- 3
            'book', elem -> 'book',
            'pagesRead', CASE WHEN elem ->> 'book' = 'Moby Dick' THEN  -- 2
                             elem -> 'pagesRead' || '"42"'
                         ELSE elem -> 'pagesRead' END
        )
    ) as new_array
FROM mytable,
    jsonb_array_elements(mydata) as elem                               -- 1
  1. Extract the array into one record per element
  2. Add a page if element contains correct book
  3. Rebuild the object
  4. Reaggregate your array.

Update would be:

UPDATE mytable
SET mycolumn = s.new_array
FROM (
    -- <query above>
) s
1
votes

Assuming you want to add a new page for the second book (Book Thief), then using JSONB_INSERT() function with the following Update Statement will be enough

UPDATE books
   SET pagesRead = JSONB_INSERT(pagesRead,'{1,pagesRead,1}','"3"'::JSONB,true)

But, in order to make it a dynamical solution, without knowing the position of the book within the main array, and adding the new page number to the end of the pagesRead array of the desired book, determine the position, and the related array's length within the subquery as

WITH b AS
(
 SELECT idx-1 AS pos1, 
        JSONB_ARRAY_LENGTH( (j ->> 'pagesRead')::JSONB )-1 AS pos2
   FROM books 
  CROSS JOIN JSONB_ARRAY_ELEMENTS(pagesRead) 
   WITH ORDINALITY arr(j,idx)
  WHERE j ->> 'book' = 'Book Thief'
)
UPDATE books
   SET pagesRead = 
        JSONB_INSERT(
                     pagesRead,
                     ('{'||pos1||',pagesRead,'||pos2||'}')::TEXT[], 
                    --# pos1 stands for the position within the main array
                    --# pos2 stands for the position within the related pagesRead array
                     '"3"'::JSONB, --# an arbitrary page number
                     true --# the new page value will be inserted after the target path
                     ) 
  FROM b

Demo