CODEWITHSUNDEEP
jsonpostgresql
Subcomandante
Subcomandante

Reputation: 403

PostgreSQL query / JSON

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 ...

Upvotes: 0

Views: 37

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51446

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

Upvotes: 1

McNets
McNets

Reputation: 10807

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

Upvotes: 1

Related Questions