LIKE expressions for string

[niquola]

Hello, big thx for jsquery!

We need some additional expressions for strings:

"path" LIKE "val%"
"path" ILIKE "%VAL%"

[akorotkov]

Hi,

In your example, "path" is field in json object or it's path with object keys like "key1.key2.key3.value"?

[niquola]

I think path with object keys. Does jsquery distincts them?

Here is real case:

"gender"  (
   "text" ILIKE "mal%" |
   "coding".# (
      "code" ilike "mal%" |
      "display" ilike "mal% 
   )
)

also cool would be:

"gender".* ilike "mal%"

[akorotkov]

In our current semantics of *

"gender".* ilike "mal%"

would be

"gender"  (
      "text" ILIKE "mal%" |
      "coding" .# (
         "code" ilike "mal%" |
         "display" ilike "mal% 
   )
)

In this case LIKE/ILIKE is matching value without key names.

[niquola]

That's ok for * semantic :)

How about ILIKE support in jsquery?

We need it for http://www.hl7.org/implement/standards/fhir/search.html - open source implementation of open HealthIT standard.

We can little-bit sponsor & collaborate on jsquery :)

[niquola]

I also saw % and $ in grammar - what are meaning of them?

[feodor]

niquola wrote:

I also saw |%| and |$| in grammar - what are meaning of them?

- any alement of array

% - any key in object

• • any path $ - current value, for example:

    a.key ($ > 10 & $ < 20) a.# ($ > 10 & $ < 20)

— Reply to this email directly or view it on GitHub https://github.com/akorotkov/jsquery/issues/1#issuecomment-45092866.

Teodor Sigaev E-mail: [email protected] WWW: http://www.sigaev.ru/

[niquola]

@feodor thanks for clarification!

[merlinm]

bump! it would be very nice to have partial string matching, even if it was only left to right.

[feodor]

We are thinking about that, but we are afraid that gin index support will be impossible. Only vodka index will be able to support it.

7 июля 2014 г. 20:27:07 CEST, Merlin Moncure [email protected] пишет:

bump! it would be very nice to have partial string matching, even if it was only left to right.

---

Reply to this email directly or view it on GitHub: https://github.com/akorotkov/jsquery/issues/1#issuecomment-48219863

Отправлено с Nokia 3210. Извините за краткость, пожалуйста.

[merlinm]

On Mon, Jul 7, 2014 at 2:02 PM, Teodor Sigaev [email protected] wrote:

7 июля 2014 г. 20:27:07 CEST, Merlin Moncure [email protected] пишет:

bump! it would be very nice to have partial string matching, even if it was only left to right.

We are thinking about that, but we are afraid that gin index support will be impossible. Only vodka index will be able to support it.

I thought as much. Precise key value searches on jsquery are unbelievably fast but I'm struggling with a good way to do range searches (if I could, I would be able to work around the limitations). I loaded a huge pg_attribute table (> 2 million records) to see how well it worked. pg_trgm tends to struggle with this type of dataset (and json generally) because of all the duplicate trigrams. The following runs in .5 ms which is just incredible IMO:

postgres=# select count(*) from foo where j @@ 'attrelid ($ = 3721083541)';

count

26

Time: 0.564 ms

However, any range search is problematic. It's evaluating first clause and double checking the returned list

postgres=# select count(*) from foo where j @@ 'attrelid ($ >= 3721083541 & $ <= 3721083541)';

count

26

(1 row)

Time: 496.856 ms

So, I have a couple of questions.

1. is there any underlying implementation choices that make optimizing the above difficult or impossible, or is it on the roadmap. I know strings are problematic, but if > and < operations were possible on strings somehow and they were fast, that would cover most use cases for me.

2. regarding VODKA index. Is there any implementation of this that I can play with?

3. (this is a bit off topic), but what is the difference between path_value and value_path?

Thanks for your quick answers!

merlin

[merlinm]

queston on path_value vs value_path is answered pretty well on the youtube video here: https://www.youtube.com/watch?v=2dQjfdXxtJw.