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.