How to use json_extract in standard sql to get a value-CodePudding

If the column has value like the below

[{"name": "my_name_1"}] [{"name": "my_name_2"}] [{"name": "my_name_3"}] [{"name": "my_name_4"}]

How do I only get my_name_1 using json_extract?

I tried the following but didnt work JSON_EXTRACT_SCALAR(column_name,"$.name")

CodePudding user response：

You can try OFFSET, cuz you have an array in column

JSON_EXTRACT_SCALAR(column_name[OFFSET(0)],"$.name")

With your data looks like this

WITH t0 AS (
    SELECT ARRAY['{"name": "my_name_1"}'] column_name
    UNION ALL 
    SELECT ARRAY['{"name": "my_name_2"}']
    UNION ALL 
    SELECT ARRAY['{"name": "my_name_3"}']
    UNION ALL 
    SELECT ARRAY['{"name": "my_name_4"}']  
)
SELECT JSON_EXTRACT_SCALAR(column_name[OFFSET(0)],"$.name") name FROM t0

CodePudding user response：

Try this one:

with mytable as (
  select '[{"name": "my_name_1"}] [{"name": "my_name_2"}] [{"name": "my_name_3"}] [{"name": "my_name_4"}]' as mycolumn
)
select JSON_EXTRACT_SCALAR(split(trim(mycolumn, ']['), '] [')[OFFSET(0)],"$.name")
from mytable