MonkeybreadSoftware Posted December 7, 2023 Posted December 7, 2023 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.
Recommended Posts