PostgreSQL, using XML with LIKE and DISTINCT

Question

After starting with XML columns as holders for key/value type of data I would like to know if PostgreSQL 'LIKE' and 'DISTINCT' could be used in such data what are my needs.

In previous question I post example table which covers needs of those topic too.

So, such query...

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1

gives wanted results by extracting all data from all nodes of stored XML's.

"{mydatabase,127.0.0.1,john,4424}"
"{herdatabase,127.0.0.1,saly,5432}"

Now I am try to filter results by value on key :

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/DBUSER/text()', description))::TEXT[] = '{saly}'::TEXT[];

And get expected result:

"{herdatabase,127.0.0.1,saly,5432}"

1) Here is problem that I don't know how to use LIKE instead of '='.

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/DBUSER/text()', description))::TEXT[] LIKE 'aly'::TEXT[];

2) Better to say I would like that my 'LIKE' search under all usable data, like this:

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/node()/text()', description))::TEXT[] LIKE 'aly'::TEXT[];

But that also don't work.

3) And to be more precise in real world here is need to filter usable data from XML with DISTINCT criteria since there will be a same data more times. Like this:

SELECT DISTINCT xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/node()/text()', description))::TEXT[] LIKE 'aly'::TEXT[];

I have described functionality now but with messy data and text column type and to get more organized data I would like to switch to XML.

Please help to get answers as needed.

EDIT Postgres 9.3, windows 7. Data for this is here: PostgreSQL, using xml

This is my query:

SELECT LAST(myindex), LAST(description), content::text, LAST(content)
  FROM (SELECT myindex, description, 
       (xpath('/setup/node()/text()', description)) AS content FROM temp1) AS alias
 WHERE content::text ILIKE '%127%'
 GROUP BY content::text;

By trying to make query by myself I see that is better to use group by than distinct.
It is a bit slow but workable.
And some experience thought, is those query OK?

Some sample data to go with the queries would be nice. Also, what's your PostgreSQL version? (Ever consider setting a non-randomly-generated username?) — Craig Ringer
– Craig Ringer, Commented Jan 29, 2014 at 2:49
That is my traditional user name with which I am connected emotively :) Of course I gave link to sample data with hope this is clear. Link points to stackoverflow.com/questions/21410412/postgresql-using-xml. In meantime I solved most of problems and only one remain. Please see EDIT in question. — Wine Too
– Wine Too, Commented Jan 29, 2014 at 8:02
Since morning is smarter than evening I solve last remaining issue and now I need only to know if is my query OK or can be written better? — Wine Too
– Wine Too, Commented Jan 29, 2014 at 8:19

user1322772 · Accepted Answer · 2014-01-29 16:35:47Z

1

You can use UNNEST() to split the XML array in your sub-query into rows and then check those values using ILIKE.

SELECT myindex, description, content
FROM (
    SELECT myindex, description, UNNEST(xpath('/setup/node()/text()', description))::TEXT AS content
    FROM temp1
    ) AS alias
WHERE content ILIKE '%127%'

I haven't benchmarked it, but performance should be better.

answered Jan 29, 2014 at 16:35

user1322772

Sign up to request clarification or add additional context in comments.

2 Comments

Wine Too Over a year ago

This is also interesting and workable approach, thanks.

Wine Too Over a year ago

Actually, that will list all DISTINCT rows, but never mind, with use of custom aggreate 'LAST' and GROUP BY I already solved that.

Collectives™ on Stack Overflow

PostgreSQL, using XML with LIKE and DISTINCT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related