google-cloud-platformgoogle-bigquery

Extracting JSON with nested `$` keys


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?


Solution

  • 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

    enter image description here

    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

    enter image description here