SQL query with json column-CodePudding

I have a table individual customer with a column employmentDetails as json field. The task is to get a customer that has empty locality field

[{
"employmentStatus": "E",
"communicationInfo": {
  "addresses": [
    {
      "id": "1",
      "houseName": "1",
      "locality": null
    }
  ]
}}]

I tried several variants with casting etc with no success, please help me to understand how to query from json object fields. My attempts below that should return some values but returned nothing.

SELECT * FROM crm."IndividualCustomer" AS ic
WHERE (ic."employmentDetails" -> 'employmentStatus')::text = 'E';

SELECT * FROM crm."IndividualCustomer" AS ic
WHERE ic."employmentDetails" -> 'communicationInfo' -> 'adresses[0]' ->> 'locality' = null;

SELECT * FROM crm."IndividualCustomer" AS ic
WHERE ic."employmentDetails" -> 'communicationInfo' -> 'adresses' ->> 'locality' = null;

CodePudding user response：

The task is to get a customer that has empty locality field

You can use a JSON path expression:

SELECT * 
FROM crm."IndividualCustomer" AS ic
WHERE ic."employmentDetails" @@ '$[*].communicationInfo.addresses[*].locality == null'

This requires Postgres 12 or later. If you have an older version, you can use the contains operator @>:

WHERE ic."employmentDetails" @> '[{"communicationInfo": {"addresses": [{"locality": null}]}}]'

This assumes that "employmentDetails" is defined as jsonb (which it should be). If it's not, you need to cast it: "employmentDetails"::jsonb.

The condition: (ic."employmentDetails" -> 'employmentStatus')::text = 'E' doesn't work, because -> returns a jsonb (or json) value which can't really be cast to a proper text value (the double quotes are kept if you do so).

You need to use the ->> operator which returns a text value directly: (ic."employmentDetails" ->> 'employmentStatus') = 'E'

However, the top level object is an array, so you would need to pick the e.g. the first array element:

(ic."employmentDetails" -> 0 ->> 'employmentStatus') = 'E'

Note the -> to return the first array element as a proper jsonb value, then the use of ->> on that value.

This can also be done using a JSON path expression to search through all array elements:

WHERE ic."employmentDetails" @@ '$[*].employmentStatus == "E"'`

CodePudding user response：

You can try to use jsonb_array_elements function, because your JSON root is an array instead of an object

SELECT *
FROM (
  SELECT *,jsonb_array_elements(employmentDetails) ->> 'employmentStatus' state
  FROM IndividualCustomer c 
)t1
WHERE state = 'E'

or use jsonb_array_elements function.

select *
from IndividualCustomer c
CROSS JOIN lateral jsonb_array_elements(employmentDetails) v
WHERE v.value ->> 'employmentStatus' ='E'

sqlfiddle