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

Question

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

I don't know your full criteria, but with the example you posted you could just strip any lowercase characters? — C B
– C B, Commented Jan 12, 2020 at 22:39
@ConnorBurton if that includes dashes, yes that would work. How would I go about that? (I'll revise question to show what I have and what I want) — foureyedraven
– foureyedraven, Commented Jan 12, 2020 at 22:41
Don't try to update by index. Just update the whole array at once, like here — Bergi
– Bergi, Commented Jan 12, 2020 at 22:47
What exactly does the jsonb column contain? An object with a grades property, or the plain array? — Bergi
– Bergi, Commented Jan 12, 2020 at 22:48
@foureyedraven my suggestion was doing this in JS, if you wanted a query solution I am not sure. In JS if you get the plain array you could do a.map(value => value.replace(/[a-z-]/g, '')) just using some regex to remove lowercase letters and hyphens — C B
– C B, Commented Jan 12, 2020 at 23:02

klin · Accepted Answer · 2020-01-13 20:24:31Z

2

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.

edited Jan 13, 2020 at 20:24

answered Jan 13, 2020 at 3:47

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

foureyedraven Over a year ago

This is fantastic, thank you!! First one works well. Glad to know more about manipulating jsonb data.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related