JSON Support

JSON documents can be created in PhixFlow using the toJson function and the output modified using the following notations.

Curly Brackets { }

Use curly brackets to create an object, e.g. { "FirstName" : "John", "LastName" : "Smith", "Age" : 50 }

Colon : and Dot .

Use a colon to define a key-value pair, e.g. { "key" : "value" }
- The key must always evaluate to be a string
- The value must always evaluate to be a string, number, object, array, boolean or null
Use a dot to access the key and value, e.g. $foo.key:$foo.value

In PhixFlow version 11.3+, JSON parsing in expressions ignores any key-value pair where the value is unavailable.

Example using curly brackets, colon and dot

Simple Example - Input

{
	// Retrieve the records from the lookup, getCo, and store each record in the variable $company
	forEach($company, getCo,

		// Using the $company object, create the key-value pairs
		toJson(
				$company.CompanyName{
					"Company ID"   : $company.CompanyID
					"Company Name" : $company.CompanyName		
					"Industry"     : $company.Industry
				// Sector is unmapped or the user running the action does not have access to the data.
				// Sector will be entirely omitted from the results
					"Sector"	   : $company.Sector
				}
		)
	)
}

Returns

{
  "Romaguera Inc" : {
    "Company ID" : 108,
    "Company Name" : "Romaguera Inc"
	"Industry" : "Manufacturing"
  },
  "Becker and Sons" : {
    "CompanyID" : 112,
	"Company Name" : "Becker and Sons"
    "Industry" : "Manufacturing"  
  },
  "Larson Inc" : {
    "CompanyID" : 398,
	"Company Name" : "Larson Inc"
    "Industry" : "Manufacturing"
  }
}

Advanced Example - Input

{
	// Convert the data from the lookup, getCo, to JSON and store it in the variable $company
	forEach($company, toJson(getCo),

		// Using the $company object, create a key-value pair where the key is the Company Name and
		// the value is an object containing two key-value pairs of the CompanyID:value and Industry:value
		$company.CompanyName : $company.CompanyID:Industry
	
	)
}

Returns

{
  "Romaguera Inc" : {
    "CompanyID" : 108,
    "Industry" : "Manufacturing"
  },
  "Becker and Sons" : {
    "CompanyID" : 112,
    "Industry" : "Manufacturing"
  },
  "Larson Inc" : {
    "CompanyID" : 398,
    "Industry" : "Manufacturing"
  }
}

At @ and Dollar $

@ can be used to reference the current object
- It allows access to any key-value pairs that have already been set
- Use the syntax @.^. to traverse upwards
$ can be used to reference the outer object of the current object being created
- It will allow access to any key-value pairs that have already been set

Example using @

Input

{

	forEach($company, toJson(getCo),

        $company.CompanyName : { 
			$company, // $company is a JSON node, so all the attributes from this node are added to the node we are building.
			"CompanyName" : toUpper(@.CompanyName) // This Company Name attribute overwrites the one in the $company JSON node
		}
	)
}

Note the use of @ as a shortcut for the current object, which is $company.

Returns

{
  "Romaguera Inc" : {
    "CompanyID" : 108,
    "CompanyName" : "ROMAGUERA INC",
    "Industry" : "Manufacturing"
  },
  "Becker and Sons" : {
    "CompanyID" : 112,
    "CompanyName" : "BECKER AND SONS",
    "Industry" : "Manufacturing"
  },
"Larson Inc" : {
    "CompanyID" : 398,
    "CompanyName" : "LARSON INC",
    "Industry" : "Manufacturing"
  }
}

Example using $

Input

{
	"Count":2,
	forEach($company, toJson(getCo),

        $.Count : {$company,"CompanyName" : toUpper($company.CompanyName)}
	)
}

Note the use of $.Count as a shortcut for the outer object of the current object being created.

Returns

{
  "Count" : 2,
  "2" : {
    "CompanyID" : 398,
    "CompanyName" : "LARSON INC",
    "Industry" : "Manufacturing"
  }
}

Date Handling

JSON support now automatically converts date and date-time values into a their corresponding string representation.

The output format is:

YYYY-MM-DDTHH:mm:ss.SSS.Z
For example: 2024-09-13T16:19:10.000Z

See Date and Time Format Patterns