« 19 years of FileMaker… | Home | MBS FileMaker Plugin,… »

Playing with JSON Query in Xojo

Let's play a bit with our new JSONMBS class in Xojo and the query function there to run a few queries.

You may want to learn a bit about JSONPath and especially the implementation we use: JsonConsJsonPath.
Let me give you a bit of a summary about JSONPath here:

JSONPath is a query language used for extracting data from JSON documents. It is similar in concept to XPath, which is used for querying XML documents. JSONPath allows you to navigate and filter through the elements and values in a JSON structure to retrieve specific data or perform operations on it.

Here's an overview of the basic syntax and features of JSONPath:

  1. Dot Notation: JSONPath uses dot notation to access properties of objects. For example, if you have a JSON object like this:

      "name": "John",
      "age": 30,
      "address": {
        "street": "123 Main St",
        "city": "New York"

    You can access the "name" property using JSONPath like this:

  2. Bracket Notation: You can also use bracket notation to access properties and array elements. For example, to access the "street" property in the above JSON:

  3. Wildcards: JSONPath supports wildcards to match multiple elements. The * wildcard can be used to match all properties or elements at a given level. For example:


    This would match both "street" and "city" in the "address" object.

  4. Recursive Descent: JSONPath allows you to navigate through nested structures using "..". For example:


    This would find all instances of "address" at any depth in the JSON structure.

  5. Filtering: JSONPath supports filtering to extract elements that meet specific criteria. For example:

    $.people[?(@.age > 25)]
  6. This would return all objects in the "people" array where the "age" is greater than 25.

  7. Slicing: You can use slicing to retrieve a range of elements from an array. For example:


    This would return elements at index 1 and 2 from the "colors" array.

  8. Functions: JSONPath supports various built-in functions for common operations. For example, you can use "@.length" to get the length of an array.

  9. Multiple Filters: You can combine multiple filters and conditions to create complex queries. For example:

    $.store.book[?(@.price < 10 && @.category == "fiction")]

    This would find books in the "store" that are in the "fiction" category and have a price less than 10.

Let's start first in Xojo with building some JSON object in our code by building book objects, putting them into arrays and

// Let's build a JSON Dim book1 As New JSONMBS book1.Value("title") = "Sayings Of the Century" book1.Value("price") = 8.95 Dim book2 As New JSONMBS book2.Value("title") = "Sword Of Honour" book2.Value("price") = 12.99 book2.Value("test") = true Dim bookArray As New JSONMBS bookArray.Append book1 bookArray.Append book2 Dim j As New JSONMBS j.Value("book") = bookArray // now show the JSON Dim t As String = j.toString(True) MessageBox j.toString(True)

Since we got a JSON now, we can run queries against it:

// run a query to find books with price < 10 and return title: dim r as JSONMBS = j.Query("$.book[?(@.price < 10)].title") Dim s As String = r.toString MessageBox s // ["Sayings Of the Century"] // run a query to find books where title starts with Sword: r = j.Query("$.book[?(@.title =~ /Sword.*?/)]") s = r.toString MessageBox s // [{"title":"Sword Of Honour","price":12.99,"test":true}] // run a query to find books second word in title is Of r = j.Query("$.book[?(tokenize(@.title,'\\s+')[1] == 'Of')].title") s = r.toString MessageBox s // ["Sayings Of the Century","Sword Of Honour"] // run a query to find all keys in second book r = j.Query("keys($.book[1])[*]") s = r.toString MessageBox s // ["title","price","test"]

Each query returns a new JSONMBS object, so you can apply multiple operation in a chain as well as using toString to output the JSON as needed to you the developer.

Please do not hesitate to contact us for questions.

13 10 23 - 12:03