JSON Functions
Overview
Table functions perform various operations on JSON documents.
Reference
jsonPathFilter
jsonPathFilter(string s, string jp) [object]
Parses input string s into a JSON document and returns a list of objects matching the JSONPath expression.
Examples:
{
"data": [
{ "d": "2018-04-24", "v": 1 },
{ "d": "2018-04-25", "v": 2 }
]
}
jsonPathFilter(s, "$.data[*].v")
Output:
[1,2]
jsonPathFilter(s, "$.data[?(@.d > '2018-04-24')]")
Output:
[{
"d":"2018-04-25",
"v":2
}]
See additional examples.
jsonToMaps
jsonToMaps(string inStr) [map]
Parses string inStr into a JSON document and returns a collection of maps containing keys and values from the JSON document.
The collection contains as many maps as there are leaf objects in the JSON document. Each map contains keys and values of the leaf object itself as well as keys and values from the parent objects.
Key names are created by concatenating the current field name with field names of its parents using . as a separator and [i] as an index suffix for array elements.
Attempts to shorten key names by removing any common prefix.
Examples:
- Common prefix
data.is discarded:
{
"data": [
{ "d": "2018-04-24", "v": 1 },
{ "d": "2018-04-25", "v": 2 }
]
}
Output:
[
{d=2018-04-24, v=1},
{d=2018-04-25, v=2}
]
- No prefix is discarded:
{
"chat_1": { "d": "2018-04-24", "v": "a" },
"chat_2": { "d": "2018-04-25", "v": "b" }
}
Output:
[
{"chat_1.d":"2018-04-24", "chat_1.v":"a",
"chat_2.d":"2018-04-25", "chat_2.v":"b"}
]
See additional examples below.
jsonToLists
jsonToLists(string inStr) [[string]]
Parses string inStr as a JSON document and converts it to a collection of lists. The first list is the header with field names, the remaining lists contain field values from the JSON document. Field values are returned as strings.
The first list in the collection contains all possible key names in the leaf objects and their parents.
The key names are created by concatenating the current field name with field names of its parents using . as a separator and [i] as an index suffix for array elements.
The function attempts to shorten key names by removing any common prefix.
The subsequent lists in the collection contain field values of the associated leaf object itself as well as field values from the parent objects ordered by keys in the first list. If the key specified in the first list is absent in the iterated object, the list on the given index contains an empty string.
Examples:
- Common prefix
data.is discarded
{
"data": [
{ "d": "2018-04-24", "v": 1 },
{ "d": "2018-04-25", "v": 2 }
]
}
Output is a collection of three lists even though the input document contains an array with two elements. The first list is the header.
[
[d, v],
[2018-04-24, 1],
[2018-04-25, 2]
]
- No prefix is discarded
{
"chat_1": { "d": "2018-04-24", "v": "a" },
"chat_2": { "d": "2018-04-25", "v": "b" }
}
Output:
[
[chat_1.d, chat_1.v, chat_2.d, chat_2.v],
[2018-04-24, a, 2018-04-25, b ]
]
See additional examples below.
flattenJson
flattenJson(string jsonStr) map
Converts the string representation of JSON document jsonStr into a map consisting of keys and values.
Processing rules:
- String
jsonStris parsed into a JSON object. IfjsonStris not a valid JSON document, the function raises an exception. - The JSON object is traversed to locate fields with primitive data types:
number,string, andboolean. - The field value is added to the map with a key set to its full name, created by appending the field local name to the full name of its parent object using
.as a separator. - If the field is an array element, its local name is set to element index
[i](indexistarts with0). - Fields with
null, empty string, empty array, and empty object values are ignored.
Input JSON document:
{
"event": "commit",
"merged": true,
"type": null,
"repo": {
"name": "atsd",
"Full Name": "Axibase TSD",
"authors": [
"john.doe",
"mary.jones",
""
]
}
}
Output map:
{
"event": "commit",
"merged": true,
"repo.name": "atsd",
"repo.Full Name": "Axibase TSD",
"repo.authors[0]": "john.doe",
"repo.authors[1]": "mary.jones"
}
Examples
The examples below are based on the following JSON document which represents output of a GraphQL query:
{
"data": {
"repository": {
"pullRequests": {
"nodes": [
{
"url": "https://github.com/axibase/atsd-api-test/pull/487",
"author": {
"login": "may_jones"
},
"mergeable": "MERGEABLE",
"baseRefName": "master",
"headRefName": "5208-series-tag-query-with-wildcard-without-entity",
"title": "5208: Series tags query with wildcard without entity"
},
{
"url": "https://github.com/axibase/atsd-api-test/pull/406",
"author": {
"login": "john_doe"
},
"mergeable": "MERGEABLE",
"baseRefName": "master",
"headRefName": "john_doe-4397",
"title": "Test #4397"
}
]
}
}
}
}
jsonToMaps(json)
[ {
"url" : "https://github.com/axibase/atsd-api-test/pull/487",
"author.login" : "may_jones",
"mergeable" : "MERGEABLE",
"baseRefName" : "master",
"headRefName" : "5208-series-tag-query-with-wildcard-without-entity",
"title" : "5208: Series tags query with wildcard without entity"
}, {
"url" : "https://github.com/axibase/atsd-api-test/pull/406",
"author.login" : "john_doe",
"mergeable" : "MERGEABLE",
"baseRefName" : "master",
"headRefName" : "john_doe-4397",
"title" : "Test #4397"
} ]
jsonToLists(json)
[
[ "url", "author.login", "mergeable", "baseRefName", "headRefName", "title" ],
[ "https://github.com/axibase/atsd-api-test/pull/487", "may_jones", "MERGEABLE", "master", "5208-series-tag-query-with-wildcard-without-entity", "5208: Series tags query with wildcard without entity" ],
[ "https://github.com/axibase/atsd-api-test/pull/406", "john_doe", "MERGEABLE", "master", "john_doe-4397", "Test #4397" ]
]
addTable(jsonToLists(json), 'ascii', true)
+---------------------------------------------------+-----------------+-----------+-------------+----------------------------------------------------+--------------------------------------------------------------+
| url | author.login | mergeable | baseRefName | headRefName | title |
+---------------------------------------------------+-----------------+-----------+-------------+----------------------------------------------------+--------------------------------------------------------------+
| https://github.com/axibase/atsd-api-test/pull/487 | may_jones | MERGEABLE | master | 5208-series-tag-query-with-wildcard-without-entity | 5208: Series tags query with wildcard without entity |
| https://github.com/axibase/atsd-api-test/pull/406 | john_doe | MERGEABLE | master | john_doe-4397 | Test #4397 |
+---------------------------------------------------+-----------------+-----------+-------------+----------------------------------------------------+--------------------------------------------------------------+
flattenJson(json)
{
"data.repository.pullRequests.nodes[0].url" : "https://github.com/axibase/atsd-api-test/pull/487",
"data.repository.pullRequests.nodes[0].author.login" : "may_jones",
"data.repository.pullRequests.nodes[0].mergeable" : "MERGEABLE",
"data.repository.pullRequests.nodes[0].baseRefName" : "master",
"data.repository.pullRequests.nodes[0].headRefName" : "5208-series-tag-query-with-wildcard-without-entity",
"data.repository.pullRequests.nodes[0].title" : "5208: Series tags query with wildcard without entity",
"data.repository.pullRequests.nodes[1].url" : "https://github.com/axibase/atsd-api-test/pull/406",
"data.repository.pullRequests.nodes[1].author.login" : "john_doe",
"data.repository.pullRequests.nodes[1].mergeable" : "MERGEABLE",
"data.repository.pullRequests.nodes[1].baseRefName" : "master",
"data.repository.pullRequests.nodes[1].headRefName" : "john_doe-4397",
"data.repository.pullRequests.nodes[1].title" : "Test #4397"
}
jsonPathFilter(json, "$..pullRequests.nodes[*][?(@.mergeable == 'MERGEABLE')]['url','author','title']")
[{
"url" : "https://github.com/axibase/atsd-api-test/pull/487",
"author":{"login":"may_jones"},
"title":"5208: Series tags query with wildcard without entity"
}, {
"url":"https://github.com/axibase/atsd-api-test/pull/406",
"author":{"login":"john_doe"},
"title":"Test #4397"
}]