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.