SQL query with json column

Question

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;

Note that the use of column or table names that require double quotes is discouraged — user330315
– user330315, Commented May 5, 2022 at 12:02

score 3 · Accepted Answer · 2022-05-05 12:18:32Z

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"'`

Collectives™ on Stack Overflow

SQL query with json column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related