CODEWITHSUNDEEP
arraysjsonpostgresqljsonb
foureyedraven
foureyedraven

Reputation: 337

How can I update all values in an array dynamically in jsonb column?

I'm working with knex in a node.js project on a PostgreSQL db. Will be using knex.schema.raw (raw SQL) to accomplish this.

I need to update all instances of a grade name ("1st", etc) in the "grades" array of a json object in a jsonb column. "grades" are one value in the whole json object, which is a lesson.

The array currently contains something like "grades": ["Pre-K", "K", "4th", "5th", "6th", "7th", "8th"] and it needs to become "grades": ["PK", "K", "4", "5", "6", "7", "8"].

Basically I need any instances of

["Pre-K", "K", "1st", 2nd", "3rd", "4th", "5th", "6th", "7th", "8th", "9th", "10th", "11th", "12th"]

to be

["PK", "K", "1", 2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"]

Is there a way to check the contents of a jsonb array, get the index of a grade, and update that index with the desired grade? Or another way to transform array data in a jsonb type column?

(My initial idea was to create a list of every permutation of combination of ["Pre-K", "K", "1st", 2nd", "3rd", "4th", "5th", "6th", "7th", "8th", "9th", "10th", "11th", "12th"], and just swap out the desired array out of data that exists, but that seems a bit ungainly, though technically possible. With this, though, I would still need to find the index of item in array in jsonb cell.)

Upvotes: 1

Views: 896

Answers (1)

klin
klin

Reputation: 121524

Use a regular expression to eliminate redundant substrings.

update my_table
set json_col = jsonb_set(
        json_col, 
        '{grades}', 
        (
            select jsonb_agg(regexp_replace(value, 're-|st|nd|rd|th', ''))
            from jsonb_array_elements_text(json_col->'grades')
        )
    );

Db<>fiddle.

A simpler and faster version (though might be considered a bit hacky):

update my_table
set json_col = jsonb_set(
        json_col,
        '{grades}',
        regexp_replace(json_col->>'grades', 're-|st|nd|rd|th', '', 'g')::jsonb
    )

Read more about POSIX Regular Expressions.

Upvotes: 2

Related Questions