Skip to main content

JSON_EXTRACT

Returns one or more values from a well-formed JSON object or array.

See also:
[SELECT] [DESCRIBE] [JSON_ARRAY_LENGTH] [ Data Types ]

tip

Use the DESCRIBE function to locate array or object datatypes which can be used with StackQL JSON functions. Use the JSON_TYPE function to resolve the datatypes of nested objects or array elements.

Syntax​

SELECT JSON_EXTRACT(json_array_or_object, json_path[, json_path1, .. json_pathN]) 
FROM <multipartIdentifier>;

Arguments​

json_array_or_object
A JSON array or object.

json_path
The JSON path to an array or object contained within an json_array_or_object. See jsonpath.com.

If a single json_path is provided then a scalar result is returned mapping to the datatype of the resolved path.

If multiple json_path arguments are provided, then the result will be an array composed of objects resolved by the repsective paths.

Specifying '$' for the json_path argument will return the entire JSON object corresponding to the column represented by the json_array_or_object argument.

Return Value(s)​

A string, integer, object, array, boolean or null depending upon the json_array_or_object and json_path provided as input.

Examples​

Return the disk objects associated with each Google Compute Engine instance​

SELECT name, json_extract(disks, '$') as disks
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';

Return the device name and size of the first disk attached to each Google Compute Engine instance​

SELECT name, json_extract(disks, '$[0].deviceName') as device_name,
json_extract(disks, '$[0].diskSizeGb') as disk_size
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';

Return the network tier of first NIC attached to each Google Compute Engine instances​

SELECT name, json_extract(networkInterfaces, '$[0].accessConfigs[0].networkTier') as network_tier
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';

Return a list of scopes associated with the first service account associated with each Google Compute Engine instance​

SELECT name, json_extract(serviceAccounts, '$[0].scopes') as scopes 
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';
/* returns a list for each service account */

For more information, see https://www.sqlite.org/json1.html#jex