How to grab certain value found in a string column?-CodePudding

I have a column that contains several different values. This is not in JSON format. It is a string that is separated into different sections. I need to grab everything that is found under ID only.

In the examples below, I only want to grab the word: "syntax" and "village"

select value.id
from TBL_A

The above does not work since this is not a json.

Does anyone know how to grab the full word that is found under the "id" section in that string column?

CodePudding user response：

Even though it's a string, since it's a properly in properly formatted JSON you can convert the string to a JSON variant like this:

select parse_json(VALUE);

You can then access its properties using standard colon and dot notations:

select parse_json(VALUE):id::string

CodePudding user response：

I would go with Greg's option of treat it as JSON because it sure looks like JSON, but if you know under some situations it most definitely is not JSON like, you could use SPLIT_TO_TABLE, and TRIM, if you know , is not inside any of the strings

SELECT t.value,
    TRIM(t.value,'{}') as trim_value,
    s.value as s_value,
    split_part(s_value, ':', 1) as token,
    split_part(s_value, ':', 2) as val,
FROM table t
    ,LATERAL SPLIT_TO_TABLE(trim_value, ',') s

Which can be compacted up, filtered with QUALIFY to get just the rows you want:

SELECT 
    SPLIT_PART(s.value, ':', 2) AS val,
FROM table t
    ,LATERAL SPLIT_TO_TABLE(TRIM(t.value,'{}'), ',') s
QUALIFTY SPLIT_PART(s.value, ':', 1) = 'id'