Skip to content

Instantly share code, notes, and snippets.

@tiebingzhang

tiebingzhang/jsonata-quick-start.md

Last active January 14, 2025 19:30
Show Gist options
  • Save tiebingzhang/5f0ad63b1bc5769a496c558694f39863 to your computer and use it in GitHub Desktop.
Save tiebingzhang/5f0ad63b1bc5769a496c558694f39863 to your computer and use it in GitHub Desktop.
Download ZIP
jsonata quick start
Raw
jsonata-quick-start.md

JSONATA one-line examples

Assuming the JSON input as following

{
    "name":"my contacts",
    "contacts":[
        {
            "firstname":"John",
            "lastname":"Doe",
            "age":75,
            "state":"CA",
            "phone numbers":{
                "mobile":"1-321-111-2222",
                "home":"1-321-222-3333"
            }
        },
        {
            "firstname":"Alice",
            "lastname":"Smith",
            "age":44,
            "state":"MD",
            "phone numbers":{
                "mobile":"1-422-111-2222",
                "home":"1-422-222-3333"
            }
        }
    ]
}

Simple Query

  1. name or $.name : my contacts
  2. contacts : the entire contacts array
  3. contacts[0] : first element in contacts (i.e. John Doe's block). Array index starts with 0.
  4. contacts[-1]: last element in contacts. (i.e. Alice's block). Going backwards, array starts with -1.
  5. contacts[-1].firstname: first name inside the last array element
  6. contacts.'phone numbers': get all the phone numbers of all contacts. quote the key if there is space or dash in the name
  7. If the root is an object {}, you can use the first level key to directly access the values, or use $.key.
  8. If the root is an array[], you can use $. to loop through the array, and use $[0] to access the first element.

Loops

  1. contacts.firstname: loop through contacts and get all firstnames in an array of just values. ["john","Alice]
  2. contacts[].firstname: same as above.
  3. contacts.{"firstname":firstname}: loop through contacts and get all firstnames in an array of objects.
  4. contacts.{"firstname":firstname, "lastname":lastname}: loop through contacts and get all firstnames and lastnames in an array of objects.
  5. contacts.{"name":firstname&" "&lastname}: loop and concatenate first name and last name together in an array of objects.
  6. contacts#$index.{"index":$index}: if you need the index of each array element, just use # followed by your variable name $index.

Filtering

  1. contacts[age>50]: output the block for John Doe as an object.
  2. contacts[firstname="Alice"]: output the block for Alice as an object.
  3. contacts[$contains(firstname,/^J/)]: select contacts with the firstname starting with J. output John Doe's block.
  4. contacts[$contains(firstname,/ce$/)]: select contacts with the firstname ending with ce. output Alice's block.

Formatting

  1. Use & to concatenate strings
  2. Use $number() to convert string to number, and use $string() to convert a number to string
  3. Use $substring(),contacts[0].'phone numbers'.mobile ~> $substring(-4) to get John Doe's mobile number's last 4 digit.
  4. Use $replace(), contacts[0].'phone numbers'.mobile ~> $replace("-","") to get John Doe's mobile number without any -.
  5. When a result of an array has a length of 1, i.e. ["a"] or [{"a":"v"}], the square brackets are removed by default. add [] at the end of the expression to keep the brackets there even with 1 element.
  6. use contacts[$contains(firstname,/^J/)].age>70?"Senior":"Young" to conditional output value based on age

Date and Time formatting

  1. use $now() to get the current time in milliseconds
  2. Use $fromMillis() format the now time (or a given time) to any format you like
  3. Use $toMillis() to convert an existing string time to milliseconds so that you can format it another way if needed.
  4. To change the date "10092024" to "2024-10-09", use $fromMillis($toMillis("10092024","[M01][D01][Y0001]"),"[Y0001]-[M01]-[D01]")
  5. Use $now('[Y0001][M01][D01]')to get the current date in the format YYYYMMDD
  6. Use $substring($.Date, 6) & $substring($.Date, 0,2) & $substring($.Date, 3,2) to convert date "MM/DD/YYYY" to "YYYYMMDD"

Example date/time format strings

$fromMillis(1510067557121, '[M01]/[D01]/[Y0001] [h#1]:[m01][P]') => "11/07/2017 3:12pm"
Y0001: Year, 4 digits
M01: Month, 2 digits,
D01: Day, 2 digits
h#1: hour, 0-12, 
m01: minute, 2 digits
P:  AM/PM, lowercase (PN would be uppercase)

$fromMillis(1510067557121, '[H01]:[m01]:[s01] [z]', '-0500') => "10:12:37 GMT-05:00"
H01: hour, 0-23, 2 digits
z: timezone

Default time format is ISO 8601: "2017-11-07T15:12:37.121Z"

See more at [https://www.w3.org/TR/xpath-functions-31/#date-picture-string]

Variables

  1. Variable name start with $
  2. Variables are assigned value with :=
  3. To use variables and functions, you likely need to enclode your code in brackets. ( your code )
  4. $$ is a built-in variable meaning the original input document

Functions

  1. Function name starts with $
  2. There are a list of bulit-in functions.
  3. You can define your own functions

Look up table

Use the following JSONATA code to build a lookup table and use it.

(
    $statetable:={ "CA":"California", "MD":"Maryland" };
    $lookup($statetable,contacts[firstname="Alice"].state)
)

Tips and examples

  1. Enclose the entire script between ( and ).
  2. The default context variable is $. You can always refer to the root of the document as $$.
  3. Variable names have a prefix of $, in both assigning values and referrence to variables. And when assigning variable values, use :=. Examples:
    • $pi := 3.1415926535897932384626;
    • $area := $pi * $r * $r
  4. Function is defined as a variable.
    • Example:
    /* Factorial is the product of the integers 1..n */
  $product := function($a, $b) { $a * $b };
  $factorial := function($n) { $n = 0 ? 1 : $reduce([1..$n], $product) };
  $sin := function($x){ /* define sine in terms of cosine */
    $cos($x - $pi/2)
  };
  5. if you have multiple statements in a function, use "(" and ")" to enclose them. Example:
        $formatDate:=function($dateString){(
          $year  := $substring($dateString,0, 4);
          $month := $substring($dateString,4, 2);
          $day   := $substring($dateString,6, 2);
          $year & "-" & $month & "-" & $day;
        )};

  1. Functions need to be placed in the beginng of the script.

  2. conditional logic looks like this: $.Price > 100 ? "Premium" : "Basic"

  3. function $substring's signature is this: $substring(str, start[, length]). start starts with zero-offset. Examples:

    $substring("Hello World", 3) // "lo World"
$substring("Hello World", 3, 5) // "lo Wo"
$substring("Hello World", -4) // "orld"
$substring("Hello World", -4, 2) // "or"

  4. Field references containing whitespace, -, or other reserved tokens can be enclosed in backticks.

    • Example: Other.`Over 18`
    • Example: Other.`LX-2400`

  5. Wildcards. Use of * instead of field name to select all fields in an object. For example Address.*, or *.Postcode.

  6. When you need to Navigate arbitrary depths, use the Descendant wildcard ** instead of *. This will traverse all descendants (multi-level wildcard).

    • Example: **.Postcode could result [ "SO21 2JN", "E1 6RF" ]
    • Exmple: To access the LX-2400_loop inside the segments array of the first transaction of the first functional_group of the first interchanges, use $segments.**."LX-2400_loop".
    • Exmple: To access the CLM-2300_loop inside the segments array of the first transaction of the first functional_group of the first interchanges, use $segments.**."LX-2300_loop"

  7. pay attention to date strings. sometimes you will need to convert the date format from YYYYMMDD to YYYY-MM-DD or vice versa.

  8. to loop through all LX-2400_loop loops, use $segments.**[`LX-2400_loop`].{( inside loop code here )}. If the LX-2400_loop is the first level, then no ** is needed, just use $segments.[`LX-2400_loop`]. Note that backtick is critical here. Double quote would not work.

  9. use **["N1-4000_loop".N1_01="BT"].*.N4_01 to access the N4_01 value insiside the N1-4000_loop that has the N1_01 value equals to "BT"

  10. use **[`PO1-8000_loop`].{"lineNumber": *.PO1_01} to go through each loop as an object, and then inside the loop, use *.key to access each object of the loop

  11. use **[`PO1-8000_loop`].[{ "lineNumber": *.PO1_01 }] (note [{) to make sure the result is an array of objects even if there is only one loop instance.

  12. if NM1-2100A_loop is an array of objects, and one object has the key N3_01, use $segments.**.`NM1-2100A_loop`.N3_01 to access the key without specifying the index of the object.

List of functions

{
  "aggregation functions": [
    "$sum(array)",
    "$max(array)",
    "$min(array)",
    "$average(array)"
  ],
  "array functions": [
    "$count(array)",
    "$append(array1, array2)",
    "$sort(array [, function])",
    "$reverse(array)",
    "$shuffle(array)",
    "$distinct(array)",
    "$zip(array1, ...)"
  ],
  "boolean functions": [
    "$boolean(arg)",
    "$not(arg)",
    "$exists(arg)"
  ],
  "date-time functions": [
    "$now([picture [, timezone]])",
    "$millis()",
    "$fromMillis(number [, picture [, timezone]])",
    "$toMillis(timestamp [, picture])"
  ],
  "higher-order functions": [
    "$map(array, function)",
    "$filter(array, function)",
    "$single(array, function)",
    "$reduce(array, function [, init])",
    "$sift(object, function)"
  ],
  "numeric functions": [
    "$number(arg)",
    "$abs(number)",
    "$floor(number)",
    "$ceil(number)",
    "$round(number [, precision])",
    "$power(base, exponent)",
    "$sqrt(number)",
    "$random()",
    "$formatNumber(number, picture [, options])",
    "$formatBase(number [, radix])",
    "$formatInteger(number, picture)",
    "$parseInteger(string, picture)"
  ],
  "object functions": [
    "$keys(object)",
    "$lookup(object, key)",
    "$spread(object)",
    "$merge(array<object>)",
    "$sift(object, function)",
    "$each(object, function)",
    "$error(message)",
    "$assert(condition, message)",
    "$type(value)"
  ],
  "string functions": [
    "$string(arg, prettify)",
    "$length(str)",
    "$substring(str, start[, length])",
    "$substringBefore(str, chars)",
    "$substringAfter(str, chars)",
    "$uppercase(str)",
    "$lowercase(str)",
    "$trim(str)",
    "$pad(str, width [, char])",
    "$contains(str, pattern)",
    "$split(str, separator [, limit])",
    "$join(array[, separator])",
    "$match(str, pattern [, limit])",
    "$replace(str, pattern, replacement [, limit])",
    "$eval(expr [, context])",
    "$base64encode()",
    "$base64decode()",
    "$encodeUrlComponent(str)",
    "$encodeUrl(str)",
    "$decodeUrlComponent(str)",
    "$decodeUrl(str)"
  ]
}

Example JSON used in the above statements

{
  "interchanges": [
    {
      "ISA_01_AuthorizationQualifier": "00",
      "ISA_02_AuthorizationInformation": "          ",
      "ISA_03_SecurityQualifier": "00",
      "ISA_04_SecurityInformation": "          ",
      "ISA_05_SenderQualifier": "01",
      "ISA_06_SenderId": "001317452TS    ",
      "ISA_07_ReceiverQualifier": "01",
      "ISA_08_ReceiverId": "006173082T     ",
      "ISA_09_Date": "190108",
      "ISA_10_Time": "0935",
      "ISA_11_StandardsId": "U",
      "ISA_12_Version": "00400",
      "ISA_13_InterchangeControlNumber": "000027415",
      "ISA_14_AcknowledgmentRequested": "0",
      "ISA_15_TestIndicator": "P",
      "functional_groups": [
        {
          "GS_01_FunctionalIdentifierCode": "PO",
          "GS_02_ApplicationSenderCode": "001317452TS",
          "GS_03_ApplicationReceiverCode": "006173082T",
          "GS_04_Date": "20190108",
          "GS_05_Time": "0935",
          "GS_06_GroupControlNumber": "27415",
          "GS_07_ResponsibleAgencyCode": "X",
          "GS_08_Version": "004010",
          "transactions": [
            {
              "ST_01_TransactionSetIdentifierCode": "850",
              "ST_02_TransactionSetControlNumber": "0008",
              "segments": [
                {
                  "BEG_01": "00",
                  "BEG_02": "NE",
                  "BEG_03": "4511798331",
                  "BEG_05": "20190108",
                  "BEG_06": "81643974"
                },
                {
                  "REF_01": "CR",
                  "REF_02": "0000267807"
                },
                {
                  "PER_01": "BD",
                  "PER_02": "Amy Henderson",
                  "PER_03": "TE",
                  "PER_04": "419-248-6391"
                },
                {
                  "FOB_01": "CC",
                  "FOB_02": "DE",
                  "FOB_03": "12345"
                },
                {
                  "SAC-1000_loop": [
                    {
                      "SAC_01": "A",
                      "SAC_02": "H850",
                      "SAC_05": "32",
                      "SAC_06": "6",
                      "SAC_07": ".25",
                      "SAC_15": "Tax"
                    }
                  ]
                },
                {
                  "DTM_01": "002",
                  "DTM_02": "20190120"
                },
                {
                  "TD5_01": "O",
                  "TD5_02": "2",
                  "TD5_03": "UPSN",
                  "TD5_04": "D",
                  "TD5_05": "UNITED PARCEL SERVICE"
                },
                {
                  "N1-4000_loop": [
                    {
                      "N1_01": "BT",
                      "N1_02": "Corner Sales",
                      "N1_03": "92",
                      "N1_04": "08923235"
                    },
                    {
                      "N3_01": "PO BOX 23507"
                    },
                    {
                      "N4_01": "HARTFORD",
                      "N4_02": "NC",
                      "N4_03": "11238"
                    }
                  ]
                },
                {
                  "N1-4000_loop": [
                    {
                      "N1_01": "ST",
                      "N1_02": "Corner Sales Plant",
                      "N1_03": "92",
                      "N1_04": "08923233"
                    },
                    {
                      "N3_01": "4520 Freddy Rd."
                    },
                    {
                      "N4_01": "Atlanta",
                      "N4_02": "GA",
                      "N4_03": "39184",
                      "N4_05": "US"
                    }
                  ]
                },
                {
                  "PO1-8000_loop": [
                    {
                      "PO1_01": "001",
                      "PO1_02": "12",
                      "PO1_03": "EA",
                      "PO1_04": "5.29",
                      "PO1_05": "PE",
                      "PO1_06": "IN",
                      "PO1_07": "05113139040",
                      "PO1_08": "VN",
                      "PO1_09": "05113139040",
                      "PO1_10": "UI",
                      "PO1_11": "00051131390409"
                    },
                    {
                      "PID-8150_loop": [
                        {
                          "PID_01": "F",
                          "PID_05": "39040 3M LEATHER - VINYL RESTORE 16"
                        }
                      ]
                    },
                    {
                      "REF_01": "CT",
                      "REF_02": "2967231"
                    },
                    {
                      "DTM_01": "002",
                      "DTM_02": "20190120"
                    }
                  ]
                },
                {
                  "CTT-9000_loop": [
                    {
                      "CTT_01": "1",
                      "CTT_02": "12"
                    },
                    {
                      "AMT_01": "TT",
                      "AMT_02": "63.57"
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment