Insert or update object for a jsonb array in postgres

Question

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!

Answer 1

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()