# JsonExtract {#JsonExtract}

## Syntax {#Syntax .section}

`jsonExtract(json, location, type)`

## Arguments {#Arguments .section}

**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 {#Details .section}

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 {#Examples .section}

Example 1. Basic usage

``` {#codeblock_ipd_l5w_bdc}
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

``` {#codeblock_ddx_l5w_bdc}
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
```

|json1|json2|
|-----|-----|
|a2|b1|

**Parent topic:**[Functions](../../Functions/category.md)

