Question 239161 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert or update object for a jsonb array in postgres

I have a table listings with a jsonb column named data.

Inside the data column I have the next structure

{
  "attributes": {
    "listings": [
      {
        "vin": "a",
        ...
      },
      {
        "vin": "b",
        ...
      }
    ]
  }
}

I am trying to figure the query to update a existing item in the listings array or insert a new one if the element doesn’t exist.

This is my try but I don’t know how to finish it.

WITH search AS (SELECT result.*
              FROM public.listings,
                   jsonb_array_elements(public.listings.data -> 'attributes' -> 'listings') WITH ORDINALITY result(value, idx)
              WHERE result.value->>'vin' = 'a')
UPDATE listings
SET data =
        CASE
            WHEN EXISTS(SELECT search.idx FROM search) THEN jsonb_set(listings.data, '{attributes,listings,' || search.idx ||'}', '{"vin": "overriding a"}')
            ELSE jsonb_set(listings.data, '{attributes,listings}', '{"vin": "new c"}')
            END
WHERE listings.id = '123';

I get the error [42P01] ERROR: missing FROM-clause entry for table "search" Position: 398

Thank you!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This query ended up working for me

    UPDATE listings
    SET data = 
        CASE
            WHEN EXISTS (
                SELECT 1 
                FROM jsonb_array_elements(listings.data -> 'attributes' -> 'listings') 
                WITH ORDINALITY result(value, idx) 
                WHERE result.value->>'vin' = '1234'
            )
            THEN jsonb_set (
                listings.data, 
                ARRAY [
                        'attributes', 
                        'listings', 
                        (   
                            SELECT idx - 1 
                            FROM jsonb_array_elements(listings.data -> 'attributes' -> 'listings') 
                            WITH ORDINALITY result(value, idx) 
                            WHERE result.value->>'vin' = '1234'
                        )::TEXT
                ], 
                :listing_element
            )
            ELSE jsonb_set (
                listings.data, 
                ARRAY['attributes', 'listings'], 
                listings.data->'attributes'->'listings' || :listing_element
            )
        END,
        updated_at = NOW()

  2. 0

    I apologize if I misunderstood the question, but just calling jsonb_set setting the parameter [, create_if_missing boolean ] wouldn’t suffice in this case?

    SELECT   
  jsonb_pretty(
   jsonb_set(data,'{attributes,listings}',
             data['attributes']['listings'] || '{"vin": "new c"}', true) 
  )
FROM listings
WHERE id = 123;

          jsonb_pretty          
--------------------------------
 {                             +
     "attributes": {           +
         "listings": [         +
             {                 +
                 "vin": "a"    +
             },                +
             {                 +
                 "vin": "b"    +
             },                +
             {                 +
                 "vin": "new c"+
             }                 +
         ]                     +
     }                         +
 }
(1 row)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search