JSON Support

Owned by Zoe Baldwin
Last updated: Oct 11, 2024
3 min read

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:

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

SeeĀ Date and Time Format Patterns