Feature Suggestion: Contains Operator

[mustafaakin]

Consider data:

    {"name": "bucket-1", "options": {"encryption": true} }
    {"name": "bucket-2", "options": {"encryption": true, "versioning": false}}

And query:

 SELECT * FROM myTable WHERE options = {'encryption': true}

The ability to compare complex data structures without expanding them is useful. However the exact match in the above case does not yield the intended result, returning only bucket-1. Of course this can be implemented via custom functions, but Postgres has the following JSONB operators https://www.postgresql.org/docs/current/functions-json.html that is useful, some examples:

jsonb @> jsonb → boolean
Does the first JSON value contain the second? 
'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb → t

jsonb ? text → boolean
Do any of the strings in the text array exist as top-level keys or array elements?
'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

The reason I'm bringing this up is that PartiQL is marketed as "First Class Nested Data", which we love, and such operators can be helpful. i'm already working on a custom functions for that for our use case but any pointers would be helpful.

[alancai98]

Hi @mustafaakin,

Could you elaborate what you mean by comparing "complex data structures without expanding them"? Also in your above query, couldn't you use the path expression in the WHERE clause? E.g. something like:

SELECT * FROM myTable WHERE options.encryption = true

We have started looking at defining a more robust standard library of functions and operators as well as the overall standard library story (e.g. PR for a PartiQL prelude https://github.com/partiql/partiql-docs/pull/3 if you're interested). Postgres certainly provides a lot of operators to process JSON. Are there any particular operators you wish existed in PartiQL?

[mustafaakin]

Sure, that example did not represent the use case clearly. Consider the following query in Postgres:

SELECT '{
  "data": "something",
  "options": {
    "encryption": true,
    "permissions": [
      "get",
      "edit",
      "list"
    ]
  }
}'::jsonb @> '{
  "options": {
    "encryption": true,
    "permissions": [
      "list",
      "get"
    ]
  }
}'::jsonb

PartiQL equivalent would be:

SELECT * FROM myTable WHERE options.encryption = true AND  'get' IN options.permissions AND 'list' in options.permissions

What I'm proposing is something similar:

SELECT * FROM myTable WHERE options @> '{encryption:true, permissions: ['list', 'get']}'

Which would mean similar to Postgres, options includes the value in the right. Once you query multiple fields, it gets easier to write in the proposed form. The reason I'm saying is that jsonb operators and functions is quite popular in Postgres to allow flexibility in a regular DB. Nothing urgent, just for consideration.