CODEWITHSUNDEEP
sqljsonregexpostgresqlreplace
Bartłomiej Tuchowski
Bartłomiej Tuchowski

Reputation: 55

How do I replace only part of string after specific string keeping part of string to replace in SQL?

I have a table named user with column data. Data contains very long string (it's a JSON nested into data column as a string). I want to change all values of fontSize key to be an int in all cases where fontSize value is a string, but the int value must be preserved. So basically this is what I want to do:

{
  "id": 1,
  "name": "New Window",
  "resources": {
    "widgets": [
      {
        "id": 1,
        "color": "#ffffff",
        "fontSize": "5",
        "width": 150
      },
      {
        "id": 2,
        "color": "#aaaaaa",
        "fontSize": "10",
        "width": 200
      }
    ]
  }
}

change to:

{
  "id": 1,
  "name": "New Window",
  "resources": {
    "widgets": [
      {
        "id": 1,
        "color": "#ffffff",
        "fontSize": 5,
        "width": 150
      },
      {
        "id": 2,
        "color": "#aaaaaa",
        "fontSize": 10,
        "width": 200
      }
    ]
  }
}

in all cases where fontSize value is a string (in some cases it is already an integer). Values of fontsize are different and they must be preserved.

I know the logic. I have to find all cases in table user where data column like '%"fontSize":"%', then delete first " after "fontSize": pattern and first " after numeric. How could I do this ? Is it possible in SQL ? Thanks for your help!

Upvotes: 0

Views: 82

Answers (1)

user330315
user330315

Reputation:

This is very ugly and shows a use-case where denormalizing a model using JSON is a bad choice (if that was a proper one-to-many relationship, the column font_size would have been defined as an integer, and you wouldn't have this problem to begin with).

The only thing I can think of, is to unnest all array elements, change the value to an integer, aggregate the "widgets" back into an array and use that to update the table.

The following code assumes there is a primary key column named id in your table.

The first step is to replace the fontSize value with a proper integer. 

select t.id, jsonb_set(w.jw, '{fontSize}', to_jsonb((w.jw ->> 'fontSize')::int))
from the_table t
  cross join jsonb_array_elements(t.the_value -> 'resources' -> 'widgets') as w(jw)

jsonb_array_elements() returns each widget as a single JSONB value (in a separate row). to_jsonb((w.jw ->> 'fontSize')::int converts the current value of the key fontSize to an integer and jsonb_set() puts that back into the JSON value.

Given your sample data this returns (including the assumed primary key column)

id | jsonb_set                                                  
---+------------------------------------------------------------
42 | {"id": 1, "color": "#ffffff", "width": 150, "fontSize": 5} 
42 | {"id": 2, "color": "#aaaaaa", "width": 200, "fontSize": 10}

Now this can be aggregated back into an array:

select t.id, jsonb_agg(jsonb_set(w.jw, '{fontSize}', to_jsonb((w.jw ->> 'fontSize')::int))) as new_widgets
from the_table t
  cross join jsonb_array_elements(t.the_value -> 'resources' -> 'widgets') as w(jw)
group by id

Given the above sample date this now returns:

id | new_widgets                                                                                                               
---+---------------------------------------------------------------------------------------------------------------------------
42 | [{"id": 1, "color": "#ffffff", "width": 150, "fontSize": 5}, {"id": 2, "color": "#aaaaaa", "width": 200, "fontSize": 10}]

This query can now be used as the source for an UPDATE statement which again uses jsonb_set() to change the value in the table.

update the_table
  set the_value = jsonb_set(the_value, '{resources, widgets}', x.new_widgets)
from (  
  select t.id, jsonb_agg(jsonb_set(w.jw, '{fontSize}', to_jsonb((w.jw ->> 'fontSize')::int))) as new_widgets
  from the_table t
    cross join jsonb_array_elements(t.the_value -> 'resources' -> 'widgets') as w(jw)
  group by id
) as x
where x.id = the_table.id;

Complete online example: https://rextester.com/OIFOBX92774

If your column is not defined as jsonb (which it should be), you need to cast it the_column::jsonb in the above queries.

Upvotes: 1

Related Questions