How to extract value from a nested JSON object key with dots

Question

I have a JSON column which have the following complex JSON Objects;

{"com.google.search.SearchWrappingSource":{"SearchWrappingContractUrn":"urn:google:SearchWrappingContract:urn:google:contract:46899","searchWrappingSourceID":"urn:li:searchSourceID:180754"}}

Nested JSON Object 2:

{"com.google.search.SearchIngestionSource":{"searchSource":"urn:li:searchSource:180754"}}

GOAL:

If the JSON Object type is like Nested JSON Object 1, I want to get the value of key "searchWrappingSourceID": urn:li:searchSourceID:180754

If the JSON Object type is like Nested JSON Object 2, I want to get the value of key "searchSource".

I tried using json_extract on Nested JSON Object, but it only outputs null.

SELECT 
json_extract(jp_source_keys, '$.com.google.search.SearchWrappingSource.SearchWrappingSourceID') as searchwrappingid_extraction
FROM table1

Is my approach correct, if not, what is the best practice to follow when dealing with complex JSON objects such as this?

Answer 1

You escape the JSON property name, for Presto/Trino syntax looks like ["prop name with special symbols"]:

-- query
with dataset(js_str) as (
    values '{
       "com.google.search.SearchWrappingSource":{
          "SearchWrappingContractUrn":"urn:google:SearchWrappingContract:urn:google:contract:46899",
          "searchWrappingSourceID":"urn:li:searchSourceID:180754"
       }
    }',
    '{"com.google.search.SearchIngestionSource":{"searchSource":"urn:li:searchSource:180754"}}'
)

-- query
select json_extract(js_str, '$["com.google.search.SearchWrappingSource"].searchWrappingSourceID') searchWrappingSourceID,
       json_extract(js_str, '$["com.google.search.SearchIngestionSource"].searchSource') searchSource
from dataset;

Or process data by parsing it into json (json_parse) and casting it to map like in this answer.