Using JSON Path for model creation

When building models from an HTTP source, you will need to provide a JSON Path expression so that Polytomic knows how to extract records from the payload responses.

JSON Path is a notation that allows you to describe which elements to retrieve from a JSON document.

Below are some commonly-used example patterns, as well as some links to further reading if you'd like to learn more.

Examples and Common Patterns

Using an array's items for records

This is useful for the Extract records field when building a model.

Take the following http response payload:

{
    "count": 2,
    "next_page_url": "https://my.api.com/users?page_token=QQbcakahe==",
    "results": [
        {
            "id": "cefcd55b-95fe-4ef3-9a89-4c50539786f3",
            "created": "2021-08-05T03:16:42.995766Z",
            "first_name": "Jane",
            "last_name": "Doe",
            "email": "[email protected]",
            "dob": "1951-09-27",
            "phone_number_home": null,
            "gender": null,
            "pronouns": null,
            "address": {
              "line_1": '440 N Barranca Ave',
              "line_2": '#1079',
              "city": 'Covina',
              "state_code": 'CA',
              "zip_code": '91723'
            }
        },
        {
            "id": "0e597d84-bb90-4d78-aa91-87403a72c003",
            "created": "2021-07-03T02:47:47.428977Z",
            "first_name": "John",
            "last_name": "Doe",
            "email": "[email protected]",
            "dob": "1960-01-17",
            "phone_number_home": "(773) 677-1843",
            "gender": null,
            "pronouns": null,
            "address": {
              "line_1": null,
              "line_2": null,
              "city": null,
              "state_code": null,
              "zip_code": null
            }
        }
    ]
}

In this case, we want to supply the results array from the document. We can do so by providing the following JSON Path expression: $.results.*.

Explanation:

  • $ selects the root of the document—all JSON Paths start with this.
  • . tells JSON Path to select a sub-element from the document.
  • results is the name of the sub-element we get
  • . again tells JSON path to select a sub element
  • * causes JSON Path to return ALL nodes of the sub-element (an array).

Extracting an individual field as a value

This is useful for extracting a "next page URL" from a payload body when setting up pagination.

Again using the document above, if we want to extract a the next_page_url field, we use $.next_page_url.

Creating Additional Fields

A field you need may not be automatically added to the model, and you may need to use JSON Path to define it.
For example if the example payload above you may want to add a field for the record's zip code. JSON Path can also be used to get the first/last items out of arrays.

Note: Polytomic will not extract example values for user-added fields. You can always run test syncs or use Preview in order to get a sense of what Polytomic will send.

367 645

Further Reading

The following resources might be helpful if you're trying to get a better understanding of JSON Path or are trying to construct more complicated expressions.