Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5.1
- 10.5.4
- 10.5
- 10.4.0

Back Next

PARSE_JSON

Parses JSON hierarchical data in a string data type and generates a struct. The struct schema is based on the specified complex data type definition that you pass in the argument. This function is appropriate when you receive hierarchical data midstream in the mapping, and need to parse the data for downstream processing.

Syntax

PARSE_JSON(upstream_string,:Type.type_definition_library.type_definition)

The following table describes the arguments for this command:

Argument	Required/Optional	Description
upstream_string	Required	The source string field, midstream in the mapping, that contains hierarchical data in JSON format.
:Type.type_definition_library.type_definition	Required	The complex data type definition that represents the schema of the struct data. Use the reference qualifier :Type to reference the type definition library that contains the complex data type definition.

Return Value

Struct.

Examples

The following expression generates a struct based on the specified complex data type definition customer_def
.

PARSE_JSON(upstream_string,:Type.type_definition_library.customer_def)

The following example shows JSON data in the upstream fields, assigned as a string data type:


{"customer" : "ABC Co", "contract" : "1010", "city" : "Phoenix",  "saleamt" : "150000.00"}
{"customer" : "Data Inc", "contract" : "1111", "city" : "Portland", "saleamt" : "20000.00"}

The complex data type definition customer_def
is defined in the type definition library as follows:

customer_def{ 
	customer : string 
	contract : int 	
	city : string 
	saleamt : int 
}

The following table shows how the PARSE_JSON function in the mapping expression parses the upstream string using customer_def
and returns a struct:

customer	contract	city	saleamt	RETURN VALUE
ABC Co	1010	Phoenix	150000.00	{ customer:ABC Co contract:1010 city:Phoenix saleamt:150000.00 }
Data Inc	1111	Portland	20000.00	{ customer:Data Inc contract:1111 city:Portland saleamt:20000.00 }

customer

contract

city

saleamt

RETURN VALUE

ABC Co

Phoenix

150000.00

{
	customer:ABC Co 
	contract:1010 
	city:Phoenix
	saleamt:150000.00
}

Data Inc

Portland

20000.00

{
	customer:Data Inc 
	contract:1111 
	city:Portland
	saleamt:20000.00
}

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

PARSE_JSON

PARSE_JSON

Syntax

Return Value

Examples