cancel
Showing results for 
Search instead for 
Did you mean: 

How to use GIN index for LIKE queries on JSON OR XML array

Highlighted
Level 2 Adventurer

How to use GIN index for LIKE queries on JSON OR XML array

I need to perform LIKE queries with only right side wildcards (i.e. xyz%) on JSON and XML arrays.

If these items were not arrays I would use a BTREE index with text_pattern_ops.

 

As they are arrays I have defined GIN indexes for them as follows:

CREATE INDEX ON test_json USING GIN ((jsondata -> 'names'));

create index ON test_xml USING GIN (CAST(xpath('//names/name/text()', xmldata) AS TEXT[]));

 

My queries return the correct data but the indexes are not used.

What is the proper way to define a GIN index for this?

 

Tags (2)
10 REPLIES 10
Highlighted
EDB Team Member

Re: How to use GIN index for LIKE queries on JSON OR XML array

Hi Mariovk,

 

Could you please elobrate more with test case, so that we can verify and suggest you with the recommendations.

Highlighted
Level 2 Adventurer

Re: How to use GIN index for LIKE queries on JSON OR XML array

Sample data:

<descriptors>

   <ids1>

      <ids>

         <id>e3bec98b-4776-4b4e-8ef3-fff41121d090</id>

         <id>8c269caa-ce3f-441c-aac3-36fc23bd671b</id>

      </ids>

   </ids1>

</descriptors>

 

 

Index:

create index test_xml_ids_gin ON test_xmldesc USING GIN (CAST(xpath('/descriptors/ids1/ids/id/text()', xml_descriptors) AS TEXT[]));

 

Query:

For equality I use the following which works fine:

select * from test_xmldesc where xpath('/descriptors/ids1/ids/id/text()', xml_descriptors)::TEXT[] @> array['8c269caa-ce3f-441c-aac3-36fc23bd671b'];

 

For LIKE I tried the following which returned correct results but the index was not used:

 

SELECT ordinality,id

FROM test_xmldesc,

     XMLTABLE('/descriptors/ids1/ids/id'

              PASSING xml_descriptors

              COLUMNS ordinality FOR ORDINALITY,

              id text PATH '.')

where id like 'e3bec98b-4776-4b4e-8ef3-fff41121d09%';

 

How would I define the GIN index so that it is used for LIKE for arrays?

Also, is there a different syntax that does not require xmltable for LIKE queries for arrays?

Highlighted
EDB Team Member

Re: How to use GIN index for LIKE queries on JSON OR XML array

Hi mariovk

 

The default GIN operator class for jsonb supports queries with top-level key-exists operators ??& and ?| operators and path/value-exists operator @>

 Please refer to the below document for more details.

https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

 

Highlighted
Level 2 Adventurer

Re: How to use GIN index for LIKE queries on JSON OR XML array

My question is specific to LIKE queries with right side wildcards - not equality.

Also, I am asking about XML - not JSON.

Highlighted
EDB Team Member

Re: How to use GIN index for LIKE queries on JSON OR XML array

Hi,

 

Please refer the following link. we hope it will help you.

 

https://www.postgresql.org/docs/9.6/pgtrgm.html

Highlighted
Level 2 Adventurer

Re: How to use GIN index for LIKE queries on JSON OR XML array

I tried created the GIN index with gin_trgm_ops.

ERROR: operator class "gin_trgm_ops" does not accept data type text[]

 

 

Highlighted
EDB Team Member

Re: How to use GIN index for LIKE queries on JSON OR XML array

Hi Mariovk,

 

Have you created extension called "pg_trgm" to work the gin_trgm_ops operator class with GIN, which is a text search feature.

 

Command:

CREATE EXTENSION pg_trgm;

 

Hope this helps you.

Level 2 Adventurer

Re: How to use GIN index for LIKE queries on JSON OR XML array

Yes I have.

The syntax only works for single-value elements TEXT but not for arrays TEXT[].

 

 

Highlighted
EDB Team Member

Re: How to use GIN index for LIKE queries on JSON OR XML array

Hi,

 

You can use array_ops for arrays.

 

For e.g.

create index gin_test_idx on gintest using gin (phones array_ops);

 

However, an index defined for an array column will never work with any(array[...]) because individual elements of arrays are not indexed.

Highlighted
Level 2 Adventurer

Re: How to use GIN index for LIKE queries on JSON OR XML array

I created the index with array_ops as you suggested:

create index test_xml_ids_gin ON test_xmldesc USING GIN (CAST(xpath('/descriptors/ids1/ids/id/text()', xml_descriptors) AS TEXT[]) array_ops);

 

The equality query uses the index:

explain SELECT ordinality,id

  FROM test_xmldesc,

       XMLTABLE('/descriptors/ids1/ids/id'

                PASSING xml_descriptors

                COLUMNS ordinality FOR ORDINALITY,

                        id text PATH '.')

where id = '8c269caa-ce3f-441c-aac3-36fc23bd671b';

 

The LIKE query does not use the index:

explain SELECT ordinality,id

  FROM test_xmldesc,

       XMLTABLE('/descriptors/ids1/ids/id'

                PASSING xml_descriptors

                COLUMNS ordinality FOR ORDINALITY,

                        id text PATH '.')

where id like '8c269caa-ce3f-441c-aac3-36fc23bd671b%';                                                            

 

This has been my question from the beginning.

Can we use the index for right hand LIKE queries on arrays or is it just for equality queries on arrays?