I have some issues with parsing column with JSON into 2 columns. My data in table looks like this:

| Column1         | user_id        |
| --------------- | -------------- |
| {"key2": "1"}   | 1ab3           |
| {"key3": "5"}   | 4jj2           |
| {"key1": "3"}   | 48gi           |
| {"key5": "4"}   | kg99           |

I was trying to use offset and regexp:

 SELECT table.user_id,
    SPLIT(kv, ': ')[OFFSET(0)] AS Column1_key_type,
    SPLIT(kv, ': ')[SAFE_OFFSET(1)] AS Column1_key_value
    FROM table,
    UNNEST(REGEXP_EXTRACT_ALL(table.Column1, r'("\w ":[^"]*)(?:,|})')) kv 

but this return me the empty table.

So, the main problem that I don't know how to write the key from JSON to first column and its value to the second one.

I'd like to parse the first column to the next view:

Thank you for your answers!

CodePudding user response：

You might consider this instead of using regexp function.

SELECT SPLIT(TRANSLATE(Column1, '{}" ', ''), ':')[OFFSET(0)] Column1_key_type,
       SPLIT(TRANSLATE(Column1, '{}" ', ''), ':')[OFFSET(1)] Column1_key_value
  FROM sample_table;