Redis JSON: Search for an empty string / null

Question

When I call the following, I get the resulting output:

> FT.CREATE Results ON JSON SCHEMA $.a AS masterId TEXT $.b AS value numeric ...
> <Insert Data>
> FT.AGGREGATE Results .* GROUPBY 1 @masterId REDUCE SUM 1 @value AS value LIMIT 0 100

1) (integer) 3
2) 1) "masterId"
   2) ""
   3) "value"
   4) "61.03"
3) 1) "masterId"
   2) "1985748"
   3) "value"
   4) "121.78"
4) 1) "masterId"
   2) "1985749"
   3) "value"
   4) "129.85"

I can then search for a list of items by specific masterId with:

> FT.SEARCH Results @masterId:1985749 LIMIT 0 1000
1) (integer) 154
...

But I cannot work out how to search for the results for the "blank" @masterId:"". Searching for double-quotes, single-quotes, NULL, etc all either return a syntax error, or 0 results. Is the correct syntax in the docs online somewhere & I'm just blind, is it some weird quirk around "empty" keys, or does it have to do with non-standard escape characters (e.g. I've tried \"\")?

score 3 · Accepted Answer · answered Aug 17 '23 at 15:46

I don't think that FT.SEARCH will allow that, however, you can use FT.AGGREGATE with a FILTER to do more advanced searches.

But first off, the masterId field should be a TAG and not a TEXT. TEXT is for full-text search. Like paragraphs of text meant for humans to read. It does lots of things to optimize for this like stemming and like ignoring punctuation and stop words.

This is probably not what you want so, for these examples, I changed the call to FT.CREATE to:

127.0.0.1:6379> FT.CREATE Results ON JSON SCHEMA $.a AS masterId TAG $.b AS value numeric
OK

Regardless, here's an example of use FT.AGGREGATE to search with a value of empty string for masterId:

127.0.0.1:6379> FT.AGGREGATE Results * LOAD 2 masterId value FILTER '@masterId == ""'
1) (integer) 3
2) 1) "masterId"
   2) ""
   3) "value"
   4) "61.03"

Note that you need to LOAD any field you would like returned or filter on.

And here is with undefined:

127.0.0.1:6379> JSON.DEL foo $.a
(integer) 1
127.0.0.1:6379> FT.AGGREGATE Results * LOAD 2 masterId value FILTER '!exists(@masterId)'
1) (integer) 3
2) 1) "value"
   2) "61.03"

Note that I'm calling an exists() function. There are a whole mess of functions for FT.AGGREGATE that you can use with APPLY that also work with FILTER.

Also, you'll probably want to do as much of your query as part of the main query string as possible as I believe that that will be more efficient. So, for example, say we wanted documents with a missing masterId and with a value between 50 and 75:

127.0.0.1:6379> FT.AGGREGATE Results '@value:[50 75]' LOAD 2 masterId value FILTER '!exists(@masterId)'
1) (integer) 1
2) 1) "value"
   2) "61.03"

Hope this helps!

I am a little surprised that seemingly FT.SEARCH doesn't appear to provide a mechanism for searching for a "blank" result; but FT.AGGREGATE will do for what I needed -- thanks for that. Our team's only recently adopted Redis as a caching solution, and I didn't design our schema, so thank you for the advice around the TAG type as well -- I'll absolutely pass that on. — Lovethenakedgun, Aug 22 '23 at 11:40

Redis JSON: Search for an empty string / null

1 Answers1