RECORD_TO_JSON

Converts a record to a JSON string. This function ignores fields calculated in LET statements and only considers fields from the source data.

Syntax

RECORD_TO_JSON(record)

Arguments

Name	Type	Description	Default Value
record	record	The record you want to convert to a JSON string.

Returns

Returns the JSON string representation of the provided record.

Example

Given a record:

{
  "fruits": ["apple", "banana", "cherry"],
  "user": {
    "first_name": "Jane",
    "last_name": "Doe",
    "location": {
      "city": "NY",
      "country": "USA"
    }
  }
}

Conversions:

• RECORD_TO_JSON(fruits) returns '["apple","banana","cherry"]'

• RECORD_TO_JSON(user) returns '{"first_name":"Jane","last_name":"Doe","location":{"city":"NY","country":"USA"}}'

• RECORD_TO_JSON(user.location) returns '{"city":"NY","country":"USA"}'

Transformation job example

CREATE JOB record_to_json_example
    ADD_MISSING_COLUMNS = true
    AS INSERT INTO default_glue_catalog.upsolver_samples.data_transformed MAP_COLUMNS_BY_NAME
    SELECT 
        RECORD_TO_JSON(fruits) AS fruits,
        RECORD_TO_JSON(user) AS user,
        RECORD_TO_JSON(user.location) AS location
    FROM default_glue_catalog.upsolver_samples.data_raw
    WHERE time_filter()
    LIMIT 1;

Query result

fruits	user	location
'["apple","banana","cherry"]'	'{"first_name":"Jane","last_name":"Doe","location":{"city":"NY","country":"USA"}}'	'{"city":"NY","country":"USA"}'