Graphql-engine: deep filtering json/jsonb data

[mybigman]

https://docs.hasura.io/1.0/graphql/manual/api-reference/graphql-api/query.html#simple-object

The documentation seems to missing information or I am going about it the wrong way... probably the later :)

I have a table with "id" and "data" being jsonb.

I cant see how to query for "order_id"

{
  tasks(where: {data: { ? }}) { // "order_id" _eq: 123456
    data
  }
}

Also how do I return all the task names?

Is it also necessary to do data(path: "") for every single field I want? Seems rather repetitive.

{
  tasks {
    order_id: data(path: "order_id")
    tasks_names: data(path: "tasks.name") // fails NULL
    tasks_names1: data(path: "tasks[*].name") // fails
  }
}

Below is the data structure.

{
  "data": {
    "tasks": [
      {
        "data": {
          "owner": "me",
          "tasks": [
            {
              "name": "task 1",
              "actioned": "yes"
            },
            {
              "name": "task 2",
              "actioned": "no"
            },
            {
              "name": "task 3",
              "actioned": "yes"
            }
          ],
          "order_id": 123456
        }
      },
      {
        "data": {
          "owner": "me",
          "tasks": [
            {
              "name": "task 1",
              "actioned": "no"
            },
            {
              "name": "task 2",
              "actioned": "no"
            },
            {
              "name": "task 3",
              "actioned": "no"
            }
          ],
          "order_id": 654321
        }
      }
    ]
  }
}

[0x777]

@mybigman You can't do this currently. What you need is a jsonpath feature that's added in the upcoming Postgres 12

[mybigman]

@0x777 damn thats a bummer. appreciate the heads up.

Can you not say create a view that it calls? I'm guessing you probably cant dynamically change which fields you want to see as it would be hardcoded in the the view :/

[0x777]

@mybigman What currently cannot be done is fetching only name from the tasks. You can fetch all tasks (path: "tasks") and then use only the necessary data on the client side.

The only other workaround I can think of is to not use a json(b) column if possible, i.e model it relationally and move the data to different tables and use relationships.

[tafelito]

what if you need to sort by a json(b) column? Is there anyway to do that now?

[Tailslide]

Just curious if this is something being looked at now that postgres 12 is released. It would be really nice to be table to query on JSONB subfields.

[maticomp]

I second this.

Are there any plans for support for querying based on fields inside a JSONB field?

Using JsonPath (Postgres 12) is one option, certainly robust one, but for many cases not the only one. Even prior to Postgres 12 JSON/JSONB operators (currently supported by Hasura) work well not only on top level object but also on subsets of structure.

Take the following JSON:

{
    "object": {
        "c": {
            "ref": 1,
            "val": 2
        },
        "m": {
            "ref": 1,
            "val": 2
        }
    },
    "array": [
        {
            "name": "c",
            "ref": 1,
            "val": 2
        },
        {
            "name": "m",
            "ref": 1,
            "val": 2
        }
    ],
    "simple": [1, 2, 3]
}

in JSONB column named test. For that, the very simple queries using JSONB operators are valid:

SELECT * FROM jsontest WHERE test ? 'object';
SELECT * FROM jsontest WHERE test @> '{"object": {"c": {}}}';

but one can also query the inside structures as follows:

SELECT * FROM jsontest WHERE test->'object' ? 'c';
SELECT * FROM jsontest WHERE test->'object' @> '{"c": {}}';
SELECT * FROM jsontest WHERE test->'object'->'c' @> '{"ref": 1}';

with JSON operators, but also plain comparions:

SELECT * FROM jsontest WHERE test->'object'->'c'->'val' = '2';

but right now in Hasura, only top level object can be queried using JSONB operator, so while:

  jsontest(where: {test: {_has_key: "object"}}) {
    test
  }

is legal, the following:

  jsontest(where: {test: {object: {_has_key: "c"}}}) {
    test
  }

is not, though Postgres supports that easily. It's possible to filter using top level field name and providing nested objects to filter as follows:

query MyQuery {
  jsontest(where: {test: {_contains: $jsonFilter}}) {
    test
  }
}

{
  "jsonFilter": {
    "object": {
      "c": {
        "val": 2
      }
    }
  }
}

but it moves complexity towards the filter query value, and while it's possible to work around JSONB operators like that, comparisons like _eq or _gt can't be used that way.

It's a bummer, as Hasura works great with subsetting JSONB on output and working with JSONB alongside other data. Without filtering capabilities of more than applying JSON operators on top level object the use cases are considerably limited.

[tirumaraiselvan]

Just FYI, you can choose the JSONPath in the result by using the path argument as shown in the reference here: https://hasura.io/docs/1.0/graphql/manual/api-reference/graphql-api/query.html#simple-object

I am changing the title to reflect the feature request more precisely.

[dnk8n]

Using the example above, is it currently possible to filter on an array within the object?, e.g. something like:

{
    "array": [
        {
            "name": "m"
        }
    ]
}

Which would do something like match one of array's list items contains an object with name "m"

My data looks more like this, each person object has an extra_data field (Postgres Jsonb) of similar format to:

{
  "travelled_to": [
    {
      "city": "Hobart",
      "country": "Australia"
    },
    {
      "city": "Cape Town",
      "country": "South Africa"
    },
    {
      "city": "Cairo",
      "country": "Egypt"
    }
  ]
}

I am struggling to see how I can query all persons who have traveled to "Cape Town" for example. Is this an example of what cannot be currently achieved?

The filter object would be nice to be something like:

{
    "travelled_to": [
        {
           "city": "Cape Town"
        }
    ]
}

But as I think @maticomp says, the filtering ability is limited. e.g. If latitude and longitude were also included, how would one filter according to point data?

Struggling to know if such features are already supported and I am just missing something.

[agoddard]

@dnk8n does your use-case work if you create a postgres view? As described here: https://dev.to/hasurahq/working-with-schemaless-data-using-graphql-on-postgres-54jn

[agoddard]

@dnk8n I think you can do the following:

(table name here is jsontable and the jsonb column is 'data')

1. Create a view in psql: CREATE VIEW travelled_to AS SELECT id, jsonb_array_elements(data->'travelled_to') as places from jsontable;

You can test to see that this returns the data you expect in psql:

jsontest=# select places from travelled_to;
                      places
--------------------------------------------------
 {"city": "Hobart", "country": "Australia"}
 {"city": "Cape Town", "country": "South Africa"}
 {"city": "Cairo", "country": "Egypt"}

1. In Hasura, make sure you enable 'Track' for the new "travelled_to" view

2. Run the query using a Query Variable, I believe you need this when the data you're querying is jsonb, you can't filter it otherwise afaik:

query getByPlace($query: jsonb) {
travelled_to( where: {
  places: {_contains: $query}
  }) {
  places
  }
}

and the query variables:

{ 
  "query": {
  "city": "Cape Town"
  }
}

That should return your expected results:

{
  "data": {
    "travelled_to": [
      {
        "places": {
          "city": "Cape Town",
          "country": "South Africa"
        }
      }
    ]
  }
}

Hopefully that helps

[dnk8n]

Thanks @agoddard I will try and implement your suggestions. Good to know that it is potentially possible to carry out what I described. Sorry if I made little sense. I struggled now to understand my own description!

Not sure I understand the original bug. What can't currently be done? (guess I don't need to understand unless I get the stage of trying to contributors which may take some months).

[agoddard]

@dnk8n haha no worries, I think the original issue was being unable to query based on properties of keys which were within a nested object in the JSON stored in JSONB. In your example, the key you want to query on is a key (city) on an element of an array which is a value of the the root level key traveled_to. So basically "I want to be able to query my data based on the properties of a nested key" is my understanding of what is solved by adding the postgresql view.

[webdeb]

postgres 12 added jsonpath, which gives postgres solid NoSQL capabilities.
You can even index the json like a boss: for example "rooms.*.id" "JSON/B" is a first-class citizen in Postgres now.

Adding support for "jsonpath" as a filter option would be great!