Is it possible to implement SHOULD with just query service

SQL++
1

The following query returns this result:
select d.name from inventory d where d.type = 'device'
Result:

[
  {
    "name": "foo"
  },
  {
    "name": "bar"
  }
]

Now if I use this query:
select d.name from inventory d where d.type = 'device' OR d.name='bar' LIMIT 1'
Results:

[
  {
    "name": "foo"
  }
]

I want to return the document with d.name='bar' if possible, but if not return any document with d.type='device'
As of right now I know I can query with AND d.name='bar', if that doesn’t return anything I re-run a query with OR d.name='bar' instead. But obviously this is not ideal and would not work well with a lot of expressions.

Is there a better way I can implement this with just the query service? I don’t want to use FTS.

2

If you need only 1 row

SELECT MAX ([d.name == "bar", d.name])[1] AS name
FROM inventory AS d
WHERE d.type = "device";

SELECT  d.name
FROM inventory AS d
WHERE d.type = "device"
ORDER BY (d.name == "bar") DESC, d.name 
LIMIT 10;
3

Thanks for the response, I need to get more than one row and I never thought about using ORDER BY that way. However if I had multiple SHOULD or i.e. if my query looked like this:

SELECT  d.meta_data.os, d.name
FROM inventory AS d
WHERE d.type = "device"
ORDER BY (d.meta_data.os == "iosxe") OR (d.name == "TEST") DESC, d.name, d.meta_data.os
LIMIT 3;

If there are lots of rows with d.meta_data.os='iosxe' and those have d.name that after “TEST” then those rows will be returned even if there is a device that matches both d.meta_data.os and d.name i.e.:

  {
    "name": "TEST",
    "os": "iosxe",
  }

Now I know I can change the ORDER BY to get my expected results:
ORDER BY (d.name == "TEST") AND (d.meta_data.os == "nxos") DESC, (d.name=="TEST") DESC, (d.meta_data.os=="nxos") DESC, d.name, d.meta_data.os

But with more fields, the query grows exponentially. Is there a function I can use to simplify this behaviour?

4

There is no easy way to do
You can follow similar technique described here How can you use array v in v for array end as where clause - #27 by vsr1