JSON_PATH
Extracts data from JSON objects.
Syntax
JSON_PATH(path, JSON)
Arguments
path
string
JSON Path Expression
JSON
string
A string that contains JSON Document
Examples
'$[*].author.name'
'[{
"name": "The Great Gatsby",
"author": {
"name": "F. Scott Fitzgerald"
}
},
{
"name": "Nineteen Eighty-Four",
"author": {
"name": "George Orwell"
}
}]'
[F. Scott Fitzgerald
, George Orwell
]
'net_id'
'{ "net_id": 41 }'
41
'net_id'
'{ "net_id": [41, 42] }'
[41,42]
'net_id[*]'
'{ "net_id": [41, 42] }'
[41
, 42
]
'net_id.parent'
'{ "net_id": [41, 42] }'
null
'$[*]'
'[1,2,3]'
[1
, 2
, 3
]
'$[*].author'
'[{
"name": "The Great Gatsby",
"author": {
"name": "F. Scott Fitzgerald"
}
},
{
"name": "Nineteen Eighty-Four",
"author": {
"name": "George Orwell"
}
}]'
[{"name":"F. Scott Fitzgerald"}
, {"name":"George Orwell"}
]
Transformation job example
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT path, JSON,
JSON_PATH('$[*].author.name', JSON) AS Output
FROM default_glue_catalog.upsolver_samples.orders_raw_data
LET path = '$[*].author.name',
JSON = '[{
"name": "The Great Gatsby",
"author": {
"name": "F. Scott Fitzgerald"
}
},
{
"name": "Nineteen Eighty-Four",
"author": {
"name": "George Orwell"
}
}]'
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
'$[*].author.name'
'[{
"name": "The Great Gatsby",
"author": {
"name": "F. Scott Fitzgerald"
}
},
{
"name": "Nineteen Eighty-Four",
"author": {
"name": "George Orwell"
}
}]'
[F. Scott Fitzgerald
, George Orwell
]
Last updated