Tweet
I am trying to use JSON_TABLE to decode a JSON document. The JSON document is a single object mostly containing single fields, but it does contain some arrays.
What I would like to do, is retrieve the values of field1, field2, and field3, and the count of elements in arr1. This would be straight forward with XML_TABLE, which uses XPATH and supports a great number of XPATH expressions for testing existence, counts, etc. But if the JSON Path expressions used by JSON_TABLE support any functions like that I cannot find them documented anywhere.
I know I could use a second SQL statement just for the array count - use "nested" to get all the array elements as individual rows, then select count() out of that. But if I can get a count off a single path expression I would prefer that.
I also know I could use YAJL and DATA-INTO instead of JSON_TABLE, but I would prefer to do this off native functions for now (hoping to avoid the headache of getting YAJL added to the library lists of all the different things that would use this program)
Code:
{
"field1":"value1",
"field2":"value2",
"field3":"value3",
"arr1":
[{
"subfield1":"value1.1",
"subfield2":"value1.2"
},
{
"subfield1":"value2.1",
"subfield2":"value2.2"
}]
}
I know I could use a second SQL statement just for the array count - use "nested" to get all the array elements as individual rows, then select count() out of that. But if I can get a count off a single path expression I would prefer that.
I also know I could use YAJL and DATA-INTO instead of JSON_TABLE, but I would prefer to do this off native functions for now (hoping to avoid the headache of getting YAJL added to the library lists of all the different things that would use this program)
Comment