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"}