Json to Table - State Solutions

Provides the ability to transform any JSON object or array into a flattened table like structure.

Version 1

HTTP Request
POST /ado/v1/JsonToTable

Header

Parameter	Description
Ocp-Apim-Subscription-Key	The subscription key you received when you purchased a plan.

Request Body

Mandatory

Parameter	Type	Description
data	object[] / object	A JSON structure.

Optional

Parameter	Type	Description
Path	string	A JPath expression that can be used to select the individual tokens to process. For further help on the required syntax, please refer to the following link … https://www.newtonsoft.com/json/help/html/SelectToken.htm Default: `$` (all tokens)
balancedOutput	string	`true` will force the resulting output to be structured as a balanced output, `false` will not. Please see the examples for more information. Note: Setting this to `true` has the potential to have an adverse impact on performance but is required if there is a desire to use the `filter`, `sortOrder` and `schema` options.

Common

Further Documentation: Common Parameters

Parameter	Type	Description
filter	String	A fully functioning SQL based WHERE statement that will filter the outgoing dataset to the records it determines as being included.
sortOrder	Object	Sorts the resulting dataset by the criteria provided. The object should contain property names that represent properties contained within the resulting dataset with a value corresponding to the desired sort direction (i.e. `ASC` or `DESC`).
schema	Object	You can use this parameter to override the inferred schema for properties in the incoming dataset(s). A field will be inferred unless specific explicitly within this object.
advancedOptions	Object	Is an object with the following properties. cultureName (String) The specified culture determines the behaviour for aspects related to formatting numeric values and dates. Is extremely important when converting strings to dates, e.g. 05/03/2022 will be treated differently between locales. For more information on the accepted values for this property, please consult the documentation from Microsoft … https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo?view=net-6.0#culture-names-and-identifiers isBoolean (string[]) A list of strings that are the names of all properties in the resulting dataset that should be treated as boolean values. By default, the framework treats boolean values as 1 (true) or 0 (false). In order to differentiate the integer as a boolean, the property in question needs to be called out explicitly in this list.

Examples

Unbalanced Output

Balanced Output

Request

{
    "data": [
        {
            "id": 2,
            "info": {
                "name": "John",
                "surname": "Smith",
                "middleName": [
                    "Andrew",
                    "Stephen"
                ]
            },
            "age": 25
        },
        {
            "id": 5,
            "info": {
                "name": "Jane",
                "surname": "Smith"
            },
            "dob": "2005-11-04T12:00:00"
        }
    ]
}
Code language: JSON / JSON with Comments (json)

Response

[
    {
        "age": 25,
        "id": 2
    },
    {
        "age": 25,
        "id": 2,
        "surname": "Smith",
        "name": "John"
    },
    {
        "age": 25,
        "id": 2,
        "surname": "Smith",
        "name": "John",
        "middleName": "Andrew"
    },
    {
        "age": 25,
        "id": 2,
        "surname": "Smith",
        "name": "John",
        "middleName": "Stephen"
    },
    {
        "dob": "2005-11-04T12:00:00",
        "id": 5
    },
    {
        "dob": "2005-11-04T12:00:00",
        "id": 5,
        "surname": "Smith",
        "name": "Jane"
    }
]
Code language: JSON / JSON with Comments (json)

Request

{
    "balancedOutput": true,
    "data": [
        {
            "id": 2,
            "info": {
                "name": "John",
                "surname": "Smith",
                "middleName": [
                    "Andrew",
                    "Stephen"
                ]
            },
            "age": 25
        },
        {
            "id": 5,
            "info": {
                "name": "Jane",
                "surname": "Smith"
            },
            "dob": "2005-11-04T12:00:00"
        }
    ]
}
Code language: JSON / JSON with Comments (json)

Response

[
    {
        "age": 25,
        "id": 2,
        "surname": null,
        "name": null,
        "middleName": null,
        "dob": null
    },
    {
        "age": 25,
        "id": 2,
        "surname": "Smith",
        "name": "John",
        "middleName": null,
        "dob": null
    },
    {
        "age": 25,
        "id": 2,
        "surname": "Smith",
        "name": "John",
        "middleName": "Andrew",
        "dob": null
    },
    {
        "age": 25,
        "id": 2,
        "surname": "Smith",
        "name": "John",
        "middleName": "Stephen",
        "dob": null
    },
    {
        "age": null,
        "id": 5,
        "surname": null,
        "name": null,
        "middleName": null,
        "dob": "2005-11-04T12:00:00"
    },
    {
        "age": null,
        "id": 5,
        "surname": "Smith",
        "name": "Jane",
        "middleName": null,
        "dob": "2005-11-04T12:00:00"
    }
]
Code language: JSON / JSON with Comments (json)