Functions in JSON Query
Let us show you the built-in functions in JSON Query and how to use them in FileMaker with MBS FileMaker Plugin.
abs
Returns the absolute value of a number.
Run abs() on a value in an array:
MBS( "JSON.Query"; "[-3]"; "abs($[0])")
[
3
]
Or find all values with an absolute value > 3, so we remove small values from the array:
MBS( "JSON.Query"; "[-3, 5, -6, 1]"; "$[?abs(@)> 3]")
[
5,
-6
]
avg
Returns the average of the items in an array of numbers.
Average up an array:
MBS( "JSON.Query"; "[1,2,9]"; "avg($)")
[
4.0
]
Or use it to find the average of values in an array of objects:
MBS( "JSON.Query"; "[{\"test\":23}, {\"test\":5}]"; "avg($[*].test)")
[
14.0
]
ceil
Returns the smallest integer value not less than a given number.
MBS( "JSON.Query"; "{}"; "ceil(2.34)")
[
3.0
]
contains
Returns true if a source array contains a search value, or a source string contains a search string.
MBS( "JSON.Query"; "[\"Hello\", \"other\", \"Lolly\"]"; "$[?(contains(@, \"ll\"))]")
[
"Hello",
"Lolly"
]
ends_with
Returns true if the source string ends with the suffix string, otherwise false.
MBS( "JSON.Query"; "[\"Hello\", \"other\", \"Lolly\"]"; "$[?(ends_with(@, \"r\"))]")
[
"other"
]
floor
Returns the largest integer value not greater than a given number.
MBS( "JSON.Query"; "{}"; "floor(2.34)")
[
2.0
]
keys
Returns an array of keys in an object.
MBS( "JSON.Query"; "{\"test\":1, \"second\":2}"; "keys($)")
[
["test", "second"]
]
length
Returns the length of an array, object or string.
MBS( "JSON.Query"; "[1,2,3]"; "length($)")
[
3
]
MBS( "JSON.Query"; "[\"Hello\"]"; "length($[0])")
[
5
]
MBS( "JSON.Query"; "{\"test\":4, \"second\":5}"; "length($)")
[
2
]
max
Returns the highest number found in an array of numbers, or the highest string in an array of strings.
MBS( "JSON.Query"; "[1,5,3]"; "max($)")
[
5
]
min
Returns the lowest number found in an array of numbers, or the lowest string in an array of strings.
MBS( "JSON.Query"; "[1,5,3]"; "min($)")
[
1
]
prod
Returns the product of the items in an array of numbers.
MBS( "JSON.Query"; "[1,5,3]"; "prod($)")
[
15.0
]
starts_with
Returns true if the source string starts with the prefix string, otherwise false.
MBS( "JSON.Query"; "[\"Hello\", \"other\", \"Lolly\"]"; "$[?(starts_with(@, \"o\"))]")#
[
"other"
]
sum
Returns the sum of the items in an array of numbers.
MBS( "JSON.Query"; "[1,5,3]"; "sum($)")
[
9.0
]
to_number
If string, returns the parsed number. If number, returns the passed in value.
MBS( "JSON.Query"; "[\"9.3\",\"12.3\"]"; "$[?(to_number(@) > 10)]")
[
"12.3"
]
tokenize
Returns an array of strings formed by splitting the source string into an array of strings, separated by substrings that match a given regular expression pattern.
Let us just split text with spaces:
MBS( "JSON.Query"; "[\"Hello World everyone!\"]"; "tokenize($[0], \" \")")
[
["Hello", "World", "everyone!"]
]
Or better find all the phone numbers in a JSON array based on the area code on the beginning:
MBS( "JSON.Query"; "[\"123-456-789\", \"555-555-5555\", \"555-123-4567\", \"987-654-3210\"]"; "$[?tokenize(@, \"-\")[0] == \"555\"]")
[
"555-555-5555",
"555-123-4567"
]
Let us know if you have questions. See also JSON Query in FileMaker.