How to use json_extract in standard sql to get a value

Question

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")

Answer 1

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

Answer 2

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

Answer 3

How do I only get my_name_1 using json_extract?

If you are looking for first name only - it is as simple as below

json_extract_scalar(column, '$[0].name')

If applied to sample data in your question - output is

Note: above trick works only for first name as it is considered valid json - till second part. after which your column is not a json anymore thus json_... functions do not work directly