JsonExtract

Syntax

Arguments

json is a LITERAL scalar or vector indicating the standard JSON string(s) to parse.

location is a scalar/vector/tuple. Each element can be a string or a non-zero integer indicating the location at the corresponding dimension.

• String: access element by key.
• Positive integer: access the n-th element from the beginning.
• Negative integer: access the n-th element from the end.

type is a string specifying the data type of the return value. It can be "long", "int", "double", or "string".

Details

This function parses extracted JSON elements into specified data type.

Return value:

• If json is a scalar, it returns a scalar; If json is a vector, it returns a vector.
• If an element corresponding to location does not exist or cannot be parsed into expected data type, NULL is returned.

Examples

Example 1. Basic usage

A = '{"a": "hello", "b": [-100, 200.5, 300], "c": { "b" : 2} }'
jsonExtract(A, [2, 1], "int") 
// output: -100

jsonExtract(A, 1, "int") 
// output: NULL

jsonExtract(A, 999, "int") 
// output: NULL

jsonExtract(A, ["b", 2], "int") 
// output: 200

jsonExtract(A, ["c", "b"], "double") 
// output: 2

B = '{"a": "hello", "b": [200, 300]}'
jsonExtract([A, B], ["c", "b"], "int") 
// output: [2, NULL]

jsonExtract([A, B], [2, -1], "int") 
// output: [300, 300]

Example 2. Use with SQL queries

A1 = '{"a": "a1","c": { "b" : 2} }'
A2 = '{"a": "a2", "c": { "b" : 3} }'
B1 = '{"a": "b1",  "c": { "b" : 3} }'
B2 = '{"a": "b2", "c": { "b" : 4} }'
t1 = table([A1, A2] as json, [2,3] as val)
t2 = table([B1, B2] as json, [3,4] as val)

select
    jsonExtract(t1.json, "a", "string") as json1, 
    jsonExtract(t2.json, "a", "string") as json2 
from t1 
join t2 on t1.val = t2.val