PostgreSQL query / JSON

Question

Given the following data:

 id | customerid |                                            orderdetails                                            |        ordertype        | orderenvelope_id 
----+------------+----------------------------------------------------------------------------------------------------+-------------------------+------------------
 10 | John Doe   | {                                                                                                 +| CUSTOMER_CREATION_ORDER |                9
    |            |   "CONTACT_DATA" : [ "de.acme.orderentry.entity.order.customerCreationOrder.ContactData", {   +|                         | 
    |            |     "contactType" : null,                                                                         +|                         | 
    |            |     "contactQuality" : "VALID",                                                                   +|                         | 
    |            |     "gender" : null,                                                                              +|                         | 
    |            |     "firstName" : "Peter",                                                                        +|                         | 
    |            |     "lastName" : "Pan"                                                                            +|                         | 
    |            |   } ],                                                                                            +|                         | 
    |            |   "CUSTOMER_DATA" : [ "de.acme.orderentry.entity.order.customerCreationOrder.CustomerData", {+|                         | 
    |            |     "customerType" : "COMPANY_ACCOUNT",                                                           +|                         | 
    |            |     "contactData" : {                                                                             +|                         | 
    |            |       "contactType" : null,                                                                       +|                         | 
    |            |       "contactQuality" : "VALID",                                                                 +|                         | 
    |            |       "gender" : null,                                                                            +|                         | 
    |            |       "firstName" : "Peter",                                                                      +|                         | 
    |            |       "lastName" : "Pan"                                                                          +|                         | 
    |            |     }                                                                                             +|                         | 
    |            |   } ]                                                                                             +|                         |

I would like to query for the records where customerType = COMPANY_ACCOUNT.

What I did so far was:

 WITH tmp AS (
    SELECT json_array_elements(orderdetails -> 'CUSTOMER_DATA') 
    AS customerdata FROM orderlist) 
SELECT * FROM tmp;

This gives the following:

---------------------------------------------------------------------------
 "de.acme.orderentry.entity.order.customerCreationOrder.CustomerData"
 {                                                                     +
    "customerType" : "COMPANY_ACCOUNT",                                +
    "contactData" : {                                                  +
    "contactType" : null,                                              +
    "contactQuality" : "VALID",                                        +
    "gender" : null,                                                   +
    "firstName" : "Peter",                                             +
    "lastName" : "Pan"                                                 +
  }                                                                    +
 }
 "de.acme.orderentry.entity.order.customerCreationOrder.CustomerData"
 {                                                                        +
    "customerType" : "PERSONAL_ACCOUNT",                                 +
    "contactData" : {                                                    +
    "contactType" : null,                                              +
    "contactQuality" : "VALID",                                        +
    "gender" : null,                                                   +
    "firstName" : "Peter",                                             +
    "lastName" : "Pan"                                                 +
  }                                                                    +
}

I have absolutely no clue, how to select the desired customerType out of this.

Update The desired result should look like the initial data set, presume you have more than one record, maybe other one like this (which should be filtered):

select * from orderlist;
id | customerid |                                            orderdetails                                            |        ordertype        | orderenvelope_id 
----+------------+----------------------------------------------------------------------------------------------------+-------------------------+------------------
 10 | John Doe   | {                                                                                                 +| CUSTOMER_CREATION_ORDER |                9
|            |   "CONTACT_DATA" : [ "de.acme.orderentry.entity.order.customerCreationOrder.ContactData", {  +|                         | 
|            |     "contactType" : null,                                                                         +|                         | 
|            |     "contactQuality" : "VALID",                                                                   +|                         | 
|            |     "gender" : null,                                                                              +|                         | 
|            |     "firstName" : "Peter",                                                                        +|                         | 
|            |     "lastName" : "Pan"                                                                            +|                         | 
|            |   } ],                                                                                            +|                         | 
|            |   "CUSTOMER_DATA" : [ "de.acme.orderentry.entity.order.customerCreationOrder.CustomerData", {+|                         | 
|            |     "customerType" : "COMPANY_ACCOUNT",                                                           +|                         | 
|            |     "contactData" : {                                                                             +|                         | 
|            |       "contactType" : null,                                                                       +|                         | 
|            |       "contactQuality" : "VALID",                                                                 +|                         | 
|            |       "gender" : null,                                                                            +|                         | 
|            |       "firstName" : "Peter",                                                                      +|                         | 
|            |       "lastName" : "Pan"                                                                          +|                         | 
|            |     }                                                                                             +|                         | 
|            |   } ]                                                                                             +|                         | 
|            | }                                                                                                  |                         | 
11 | John Doe   | {                                                                                                 +| OFFER_PURCHASE_ORDER    |                9
|            |   "CURRENCY_CODE" : "EUR",                                                                        +|                         | 
|            |   "DURATION" : [ "java.util.Date", 1498222579906 ]                                                +|                         | 
|            | }                                                                                                  |                         | 
13 | Jane Doe   | {                                                                                                 +| CUSTOMER_CREATION_ORDER |               12
|            |   "CONTACT_DATA" : [ "de.acme.orderentry.entity.order.customerCreationOrder.ContactData", {  +|                         | 
|            |     "contactType" : null,                                                                         +|                         | 
|            |     "contactQuality" : "VALID",                                                                   +|                         | 
|            |     "gender" : null,                                                                              +|                         | 
|            |     "firstName" : "Peter",                                                                        +|                         | 
|            |     "lastName" : "Pan"                                                                            +|                         | 
|            |   } ],                                                                                            +|                         | 
|            |   "CUSTOMER_DATA" : [ "de.acme.orderentry.entity.order.customerCreationOrder.CustomerData", {+|                         | 
|            |     "customerType" : "PERSONAL_ACCOUNT",                                                          +|                         | 
|            |     "contactData" : {                                                                             +|                         | 
|            |       "contactType" : null,                                                                       +|                         | 
|            |       "contactQuality" : "VALID",                                                                 +|                         | 
|            |       "gender" : null,                                                                            +|                         | 
|            |       "firstName" : "Peter",                                                                      +|                         | 
|            |       "lastName" : "Pan"                                                                          +|                         | 
|            |     }                                                                                             +|                         | 
|            |   } ]                                                                                             +|                         | 
|            | }                                                                                                  |                         | 
14 | Jane Doe   | {                                                                                                 +| OFFER_PURCHASE_ORDER    |               12
|            |   "CURRENCY_CODE" : "EUR",                                                                        +|                         | 
|            |   "DURATION" : [ "java.util.Date", 1498457363451 ]                                                +|                         | 
|            | }                                                                                                  |                         |

Only record with id = 10 should be returned

Sorry for not being precise ...

I know about the strange JSON ... but I need it like this for correct deserialization to Java by Jackson ... — Subcomandante
– Subcomandante, Commented Jun 26, 2017 at 8:39

McNets · Accepted Answer · 2017-06-26 09:07:16Z

I've added a new item according to your data:

drop table if exists orderlist;
create table if not exists orderlist(id int, customerid varchar(20), orderdetails json);
insert into orderlist values (10, 'John Doe', 
'{"CONTACT_DATA" : [ "de.oneandone.acme.entity.order.customerCreationOrder.ContactData", 
                    {"contactType" : null,
                     "contactQuality" : "VALID",
                     "gender" : null,
                     "firstName" : "Peter",
                     "lastName" : "Pan"
                    } 
                   ],
  "CUSTOMER_DATA" : [ "de.oneandone.orderentry.entity.order.customerCreationOrder.CustomerData", 
                    { "customerType" : "COMPANY_ACCOUNT",
                      "contactData" : {"contactType" : null,
                                       "contactQuality" : "VALID",
                                       "gender" : null,
                                       "firstName" : "Peter",
                                       "lastName" : "Pan"
                                      }
                    },
                    { "customerType" : "PERSONAL_ACCOUNT",
                      "contactData" : {"contactType" : null,
                                       "contactQuality" : "VALID",
                                       "gender" : null,
                                       "firstName" : "Peter",
                                       "lastName" : "Pan"
                                      }
                    } 
                    ]
 }'::json); 

WITH js AS
(
  SELECT json_array_elements(orderdetails->'CUSTOMER_DATA') AS customerdata 
  FROM orderlist
)
SELECT *
FROM   js
WHERE  customerdata->>'customerType' = 'COMPANY_ACCOUNT'
;

drop table if exists orderlist;

This is the result:

    customerdata
1   { "customerType" : "COMPANY_ACCOUNT",
                      "contactData" : {"contactType" : null,
                                       "contactQuality" : "VALID",
                                       "gender" : null,
                                       "firstName" : "Peter",
                                       "lastName" : "Pan"
                                      }
                    }

Rextester here

Altough - and due to my unprecise question - this does not solve THIS problem, it solves another one of mine. Thx a lot ...

Vao Tsun · Accepted Answer · 2017-06-26 09:21:30Z

1

try this?

WITH tmp AS (
    SELECT *,json_array_elements(orderdetails -> 'CUSTOMER_DATA') AS customerdata FROM orderlist) 
SELECT  id,customerid,orderdetails, ordertype, orderenvelope_id 
FROM tmp
WHERE customerdata->>'customerType' = 'COMPANY_ACCOUNT'

keep in mind id, customerid will repeat as many times as many array elements with your search patter will be in orderdetails - in your data sample it should be two

edited Jun 26, 2017 at 9:21

answered Jun 26, 2017 at 8:32

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

4 Comments

Subcomandante Over a year ago

Unfortunately not working ... (columns: id, customerid don't exist)

Vao Tsun Over a year ago

yes - forgot to mention them. try the updated. If not what you want please show wanted result in the original post

Subcomandante Over a year ago

Error: column »json_array_elements« doesn't exist Row 6: WHERE json_array_elements->>'customerType' = 'COMPANY_ACCOUN...

Vao Tsun Over a year ago

thanks. and also I did not notice your alias previously. updated the query

Collectives™ on Stack Overflow

PostgreSQL query / JSON

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related