How can I extract values from a json string-CodePudding

I need to extract values from the following json string:

{
  "person": {
    "foo": {
      "Point": {
        "Value": "27.8",
        "Id": "bar"
      }
    }
  }
}

The results should be a table:

person	Point	Id
foo	27.9	bar

CodePudding user response：

You may consider below since a person name is being used as a key of json object.

CREATE TEMP FUNCTION json_keys(input STRING) RETURNS Array<String> 
LANGUAGE js AS """
  return Object.keys(JSON.parse(input));
""";

CREATE TEMP FUNCTION json_values(input STRING) RETURNS String
LANGUAGE js AS """
  return JSON.stringify(Object.values(JSON.parse(input))[0])
""";

WITH json_data AS (
  SELECT '{"person": {"foo": {"Point": { "Value": "27.8", "Id": "bar" }}}}' json
)
SELECT json_keys(p)[SAFE_OFFSET(0)] AS Person,
       JSON_VALUE(json_values(p), '$.Point.Value') AS Point,
       JSON_VALUE(json_values(p), '$.Point.Id') AS Id
  FROM json_data, UNNEST([JSON_QUERY(json, '$.person')]) p;

 -------- ------- ----- 
| Person | Point | Id  |
 -------- ------- ----- 
| foo    |  27.8 | bar |
 -------- ------- -----

Or using regexp functions if a json has fixed schema.

SELECT REGEXP_EXTRACT(json, r'"person"\s*:\s*{\s*"(.*?)"\s*:') AS Person,
       REGEXP_EXTRACT(json, r'"Value"\s*:\s*"(.*)"\s*,') AS Point,
       REGEXP_EXTRACT(json, r'"Id"\s*:\s*"(.*)"') AS Id,
  FROM json_data;

CodePudding user response：

Consider also below example - hope you can apply to your use case

WITH your_table AS (
  SELECT '{"person": {"foo": {"Point": { "Value": "27.8", "Id": "bar" }}}}' json_string
)
select json_string,  
  regexp_extract(json_string, r'"person": {"([^"] )":') as person,
  json_value(point, '$.Value') as point,
  json_value(point, '$.Id') as id
from your_table, 
unnest([regexp_extract(json_string, r'"person": {"[^"] ": {"Point": ({[^{}] })}}')]) as point

with output