Postgres JSONB Editing columns

Question

I was going through the Postgres Jsonb documentation but was unable to find a solution for a small issue I'm having.

I've got a table : MY_TABLE

that has the following columns:

User, Name, Data and Purchased

One thing to note is that "Data" is a jsonb and has multiple fields. One of the fields inside of "Data" is "Attribute" but the values it can hold are not in sync. What I mean is, it could be a string, a list of strings, an empty list, or just an empty string. However, I want to change this.

The only values that I want to allow are a list of strings and an empty list. I want to convert all the empty strings to empty lists and regular strings to a list of strings.

I have tried using json_build_array but have not had any luck

So for example, I'd want my final jsonb to look like :

       [{
           "Id": 1,
           "Attributes": ["Test"]

       },
       {
           "Id": 2,
           "Attributes": []

       },
{
           "Id": 3,
           "Attributes": []

       }]

when converted from

    {
        "Id": 1,
        "Attributes": "Test"

    },
{
           "Id": 2,
           "Attributes": ""

       },
{
           "Id": 3,
           "Attributes": []

       }
]

I only care about the "Attributes" field inside of the Json, not any other fields.

I also want to ensure for some Attributes that have an empty string "Attributes": "", they get mapped to an empty list and not a list with an empty string ([] not [""])

I also want to preserve the empty array values ([]) for the Attributes that already hold an empty array value.

This is what I have so far:

jsonb_set(
    mycol,
    '{Attributes}',
    case when js ->> 'Attributes' <> '' 
        then jsonb_build_array(js ->> 'Attributes')
        else '[]'::jsonb
    end
)

However, Attributes: [] is getting mapped to ["[]"]

Marth · Accepted Answer · 2020-05-25 16:35:54Z

Use jsonb_array_elements() to iterate over the elements of each 'data' cell and jsonb_agg to regroup the transform values together into an array:

WITH test_data(js) AS (
    VALUES ($$ [
      {
        "Id": 1,
        "Attributes": "Test"
      },
      {
        "Id": 2,
        "Attributes": ""
      },
      {
        "Id": 3,
        "Attributes": []
      }
    ]
    $$::JSONB)
)
SELECT transformed_elem
FROM test_data
JOIN LATERAL (
    SELECT jsonb_agg(jsonb_set(
            elem,
            '{Attributes}',
            CASE
                WHEN elem -> 'Attributes' IN ('""', '[]')          THEN '[]'::JSONB
                WHEN jsonb_typeof(elem -> 'Attributes') = 'string' THEN jsonb_build_array(elem -> 'Attributes')
                ELSE elem -> 'Attributes'
            END
        )) AS transformed_elem
    FROM jsonb_array_elements(test_data.js) AS f(elem) -- iterate over every element in the array
) s
 ON TRUE

returns

[{"Id": 1, "Attributes": ["Test"]}, {"Id": 2, "Attributes": []}, {"Id": 3, "Attributes": []}]

Collectives™ on Stack Overflow

Postgres JSONB Editing columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related