EN VI

Parsing key-value pairs, selecting based on value, and creating new column with SQL?

How to Parsing key-value pairs, selecting based on value, and creating new column with SQL

I have a my_table like this. Col2 is a string type:

| Col1         | Col2                               |
|--------------|------------------------------------|
| some_string  |{siblings:0, pets:1, have_friends:0}|
| some_string  |{siblings:0, pets:0, have_friends:0}|
| some_string  |{siblings:1, pets:2, have_friends:3}|
| some_string  |{siblings:1, pets:1, have_friends:0}|

I would like to use SQL to parse the key:value pairs on Col2, and filter out pairs where the value is "0" so that the output is:

| Col1         | Col2                               |
|--------------|------------------------------------|
| some_string  |{pets:1}                            |
| some_string  |{}                                  |
| some_string  |{siblings:1, pets:2, have_friends:3}|
| some_string  |{siblings:1, pets:1}                |

Would someone help?

Solution:

So, let's create the schema:

create table mytable(col1 text, col2 text);

insert into mytable(col1, col2)
values
('some_string', '{key1:0, key2:1, key3:0}'),
('some_string', '{key1:0, key2:0, key3:0}'),
('some_string', '{key1:1, key2:2, key3:3}'),
('some_string', '{key1:1, key2:1, key3:0}');

In order to compute the values you desire, use

select col2 as input, regexp_replace(col2, '(, )?key\d:0(, )?', '', 'g') as output
from mytable;

if you want to select them. regexp_replace replaces the match in a string (param1) of a pattern (param2) with a new string (param3). The g specifies that all matches are to be replaced.

Fiddle:

https://www.db-fiddle.com/f/omvF3XLrmCpYM79YYTA1wH/0

And if you need to update the values, then you can:

update mytable
set col2 = regexp_replace(col2, '(, )?key\d:0(, )?', '', 'g');

Fiddle: https://www.db-fiddle.com/f/omvF3XLrmCpYM79YYTA1wH/1

EDIT

You can enhance the regex of the patter in the way you prefer. If you are to support any characters that are lower or upper case as well as numbers and underscore, try:

select col2 as input, regexp_replace(col2, '(, )?[a-z_A-Z0-9]*:0(, )?', '', 'g') as output
from mytable;

Fiddle: https://www.db-fiddle.com/f/omvF3XLrmCpYM79YYTA1wH/2