Links

JSON_TO_RECORD

Description: Extracts data from JSON objects.

Syntax

JSON_TO_RECORD(mappings, outputArray, value)

Arguments

Name
Type
Description
Default Value
mappings
string
JSON to field mappings
outputArray
boolean
If the string contains multiple JSON records, use this to allow outputting all of them
false
value
string

Input Record Example:

{
"field1": "Hello",
"field2": "World",
"field3": 1579540882000
}

Examples

mappings
outputArray
value
Output
'field1,field1,string'
false
'{ "field1": "Hello", "field2": "World", "field3": 1579540882000 }'
{field1: "Hello"}
'field3,field3,number'
false
'{ "field1": "Hello", "field2": "World", "field3": 1579540882000 }'
{field3: 1579540882000}
['field1,field1,string', 'field2,field2,string']
false
'{ "field1": "Hello", "field2": "World", "field3": 1579540882000 }'
{field1: "Hello", field2: "World"}

Transformation job example

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 value,
JSON_TO_RECORD('field1,field1,string', false, value) AS Output1,
JSON_TO_RECORD(['field1,field1,string', 'field2,field2,string'], false, value) AS Output2
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

value
Output1
Output2
'{ "field1": "Hello", "field2": "World", "field3": 1579540882000 }'
{field1: "Hello"}
{field1: "Hello", field2: "World"}