How to extract a value from json in presto?

@ryan.murray 

In Presto, you can extract a value from a JSON object using the json_extract_scalar function. Here is an example of how to use this function to extract a specific value from a JSON string:

1

SELECT json_extract_scalar('{"name": "John", "age": 30}', '$.name') as name;

In this example, the JSON string {"name": "John", "age": 30} is passed as the first argument to the json_extract_scalar function, and $name is used as the JSON Path to extract the value of the key "name" from the JSON object. The result will be "John".

You can also extract values from nested JSON objects by providing a more complex JSON Path. For example:

1

SELECT json_extract_scalar('{"person": {"name": "Alice", "age": 25}}', '$.person.name') as name;

In this query, the value of the key "name" inside the nested object "person" is extracted, resulting in "Alice".

You can also use json_extract function to extract and return the entire JSON object instead of just a scalar value:

1

SELECT json_extract('{"name": "John", "age": 30}', '$.name') as name;

This will return the JSON object {"name": "John"}.