ph_flattenjson PowerHome formula function
Description
Takes a string containing valid JSON data and flattens it into a list of name/value pairs with the specified separator 
Syntax
ph_flattenjson ( as_json, as_sep, ai_flags, ai_type )
Argument Description
as_json String. A string containing valid JSON
as_sep String. The separator character(s) you would like to use for the name/value pairs
ai_flags Integer. Flags controlling how the JSON is flattened. See Usage below
ai_type Integer. The particular method to use for the flattening of JSON data. Valid values are 0, 1, and 2
Return value
String. Returns the JSON data flattened into a name/value pair string with 1 name/value pair per line. If an error occurs, returns a string starting with "*ERROR*" followed by a description of the error 
Usage
Use this function to "flatten" JSON data into name/value pairs such that other functions within PowerHome (such as ph_extractval or the regular expression functions) can be used to extract specific data. 

The ai_type parameter controls the specific method used to flatten the JSON data with each method returning potentially different results. Type 0 uses a character by character parsing routine to flatten the JSON data. Type 1 uses a C# dictionary and JTokens to flatten the JSON. Type 2 uses a C# dictionary and JObjects to deserialize and flatten the JSON.

Each of the 3 types (0, 1, 2) allow different ai_flag parameters to be specified to control the operation. Not all flags are supported in all types. The supported flag values and which types they apply to are listed below. The desired flag values are added together to achieve the final result to be passed in the ai_flags parameter:

  • 1 - Encloses the flattened JSON NAME in double quotes. Supported by types 0, 1, and 2.
  • 2 - Encloses the flattened JSON VALUE in double quotes. Supported by types 0, 1, and 2.
  • 4 - Strip commas contained within a JSON NAME. Supported by type 0.
  • 8 - Strip commas contained within a JSON VALUE. Supported by type 0.
  • 16 - Strip double quotes contained with a JSON VALUE. Supported by type 0.
    • Examples
    The following examples demonstrate typical syntax/usage for this function.
    Assume the file named c:\powerhome\json.txt contains the following JSON data: {"people":[{"name":"John","age":31,"city":"New York","phones":["4075551212","3215551212","8885551212"]}]}

    • ph_flattenjson(ph_readfile("c:\powerhome\json.txt"),":",0,0) will return:
    people.name:John
    people.age:31
    people.city:New York
    people.phones:[4075551212,3215551212,8885551212]

    • ph_flattenjson(ph_readfile("c:\powerhome\json.txt"),":",0,1) will return:
    people.0.name:John
    people.0.age:31
    people.0.city:New York
    people.0.phones.0:4075551212
    people.0.phones.1:3215551212
    people.0.phones.2:8885551212

    • ph_flattenjson(ph_readfile("c:\powerhome\json.txt"),":",0,2) will return:
    people[0].name:John
    people[0].age:31
    people[0].city:New York
    people[0].phones[0]:4075551212
    people[0].phones[1]:3215551212
    people[0].phones[2]:8885551212