I'm looking for a method to parse a JSON with nested keys containing the $
symbol.
with example as (
select '{"id1":{"$id2":"12345"},"something":{"$id3":{"$id4":"67890"}}}' as json_col
)
, parsed as (
select json_keys(parse_json(json_col)) as parsed_json_col from example
)
select
json_query(parsed_json_col, '$.id1').`$id2` as id2 -- this works
, json_query(parsed_json_col, '$.something').`$id3`.`$id4` as id4 -- this doesn't
from parsed
The dot-notation after the json_query
feels a little odd for the first one, and doesn't work for the second version. Can I extract all fields using a different function?
below should work
WITH example AS (
SELECT '{"id1":{"$id2":"12345"},"something":{"$id3":{"$id4":"67890"}}}' AS json_col
)
, parsed AS (
SELECT PARSE_JSON(json_col) AS parsed_json_col FROM example
)
SELECT
JSON_QUERY(parsed_json_col, '$.id1').`$id2` AS id2 -- this works
, JSON_QUERY(parsed_json_col, '$.something').`$id3`.`$id4` AS id4 -- this ALSO works
FROM parsed
with output
Also, you can use below version
SELECT
parsed_json_col.id1.`$id2` AS id2 -- this works as well
, parsed_json_col.something.`$id3`.`$id4` AS id4 -- this works as well,
FROM parsed
with very same output