Postgresql Query to select entry keys in bytea field

Question

I'm working with a Postgresql DB. table is ticketlines containes a bytea field called attributes

Sample of field contents in JSON view:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
    <properties>
    <comment>Ticket Attributes</comment>
    <entry key="product.candiscount">true</entry>
    <entry key="product.categoryid">8410a5e3-fc31-4370-b7da-d8406d80ca97</entry>
    <entry key="product.kitchen">false</entry>
    <entry key="product.com">false</entry>
    <entry key="product.alwaysavailable">false</entry>
    <entry key="product.service">true</entry>
    <entry key="product.warranty">false</entry>
    <entry key="product.taxcategoryid">000</entry>
    <entry key="product.vprice">false</entry>
    <entry key="product.discounted">no</entry>
    <entry key="notes">006942</entry>
    <entry key="product.texttip"/>
    <entry key="product.nosc">0</entry>
    <entry key="product.promotionadded">false</entry>
    <entry key="product.name">Pawn Payout</entry>
    <entry key="product.code">pp</entry>
    <entry key="product.ispack">false</entry>
    <entry key="product.alias"/>
    <entry key="product.verpatrib">false</entry>
    <entry key="product.managestock">true</entry>
    </properties>

Is there a simple way to query this data for reporting in a SELECT statement (ie; SELECT product.name, notes ) ?

I'm close with this: but obviously have the structure wrong: SELECT xpath('//properties:notes/text()', CAST(convert_from(attributes, 'UTF-8') AS XML)) FROM ticketlines where ticket='7abc41d9-0d7f-4187-bb81-e4139d7728bf'

the results are: ERROR: invalid XML content DETAIL: line 2: StartTag: invalid element name ^ *** Error *** ERROR: invalid XML content SQL state: 2200N Detail: line 2: StartTag: invalid element name

It's from pglobeditor.exe, The xml, json, and text modes all look this way. The application runs in Java, and I assume that JSON is how it is done. — Aaron Mills, Jul 07 '20 at 11:36
This section of text, is simply here to show the contents of the bytea field, with the intent of someone to assist querying the data out of the field, no intention to argue over layout differences between XML, text, Jason, or whatever. — Aaron Mills, Jul 07 '20 at 23:49
Then the answer is a resounding "no". If the structure of the data is unspecified, you cannot expect to be able to extract elements from it. — Laurenz Albe, Jul 08 '20 at 05:25
Digging deeper, it does appear to be stored as XML. The field header reads: — Aaron Mills, Jul 08 '20 at 22:42
I'm close with this: but obviously have the structure wrong: SELECT xpath('//properties:notes/text()', CAST(convert_from(attributes, 'UTF-8') AS XML)) FROM ticketlines where ticket='7abc41d9-0d7f-4187-bb81-e4139d7728bf' the results are: ERROR: invalid XML content DETAIL: line 2: StartTag: invalid element name ^ *** Error *** ERROR: invalid XML content SQL state: 2200N Detail: line 2: StartTag: invalid element name — Aaron Mills, Jul 08 '20 at 23:10

score 0 · Answer 1 · answered Jul 11 '20 at 07:51

0

I did recreate your table from what you wrote and had success with this query:

SELECT xpath('/properties/entry[@key=''notes'']/text()', CAST(convert_from(attributes, 'UTF-8') AS XML)) AS result FROM ticketlines where ticket  =1;

Though I did not reproduce your error message with your query. Please check what the real content of the ticket 7abc41d9-0d7f-4187-bb81-e4139d7728bf in your DB-Table is.

answered Jul 11 '20 at 07:51

Alex Funk

346
2
7

Thanks.Still no go. I ran query as: SELECT xpath('/properties/entry[@key=''notes'']/text()', CAST(convert_from(attributes, 'UTF-8') AS XML)) AS result FROM ticketlines where ticket ='7abc41d9-0d7f-4187-bb81-e4139d7728bf'; RESULT: ERROR: invalid XML content DETAIL: line 2: StartTag: invalid element name ^ ********** Error ********** ERROR: invalid XML content SQL state: 2200N Detail: line 2: StartTag: invalid element name – Aaron Mills Jul 12 '20 at 19:05
What version of Postgres do you use? Mine was 11.5. You can check by SELECT Version(); in SQL when in doubt. A quick internet search yield a post from 2010 with a similar issue with DOCTYPE in Postgres. – Alex Funk Jul 12 '20 at 20:32
Version 9.4.5 also, here is a link to a backup of that table: https://drive.google.com/file/d/196LfpsClRXL64TXGkOh3F4Dn_7GwIEH1/view?usp=sharing – Aaron Mills Jul 13 '20 at 21:41
Your postgres Version 9.4.5 is old. If I downgrade my docker container to a postgres:9.4.5 image I can reproduce your error message otherwise I can not. – Alex Funk Jul 14 '20 at 10:29
Thank you.I will work on migrating to a newer version. – Aaron Mills Jul 14 '20 at 11:26
Upgrade to v9.5 solved the error. Thank you again. Kudos and 10,000 points to you. – Aaron Mills Jul 14 '20 at 11:39

score 0 · Answer 2 · answered Jul 16 '20 at 15:34

It seems like the contents of the bytea are no proper XML - look at the convert_from result.

With the XML you show in your question, the correct XPath query would be

SELECT xpath('//entry[@key=''notes'']/text()', xml) FROM ...

That would get the contents of the "entry" element where the value of the "key" attribute is notes.

Postgresql Query to select entry keys in bytea field

2 Answers2