JSON_TO_RECORD

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 outputing all of them	true
value	string

Examples

mappings	outputArray	value	Output
'a,a,string'	true	'{ "a": "Hello" }'	{a: Hello}
'a,a,number'	true	'{ "a": 1579540882000 }'	{a: 1579540882000}
'a.value,a.value,string
b.value,b.value,string'	true	'{ "a": { "value": "Hello" }, "b" : { "value": "World" } }'	{a.value: Hello, b.value: World}
'a.value,a.value,string
b.value,b.value,string /this is a comment inside the mapping/'	true	'{ "a": { "value": "Hello" }, "b" : { "value": "World" } }'	{a.value: Hello, b.value: World}
'a.value,a.value,string
b.value,b.value,string'	true	'{ "a": null::string, "b" : { "value": "World" } }'	{a.value: null, b.value: World}

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 mappings, outputArray, value,
        JSON_TO_RECORD('a,a,string', true, value) AS Output
    FROM default_glue_catalog.upsolver_samples.orders_raw_data
    LET mappings = 'a,a,string',
        outputArray = true,
        value = '{ "a": "Hello" }'
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result

mappings	outputArray	value	Output
'a,a,string'	true	'{ "a": "Hello" }'	{a: Hello}