CODEWITHSUNDEEP
sqlarraysjsonpostgresqlexists
Shamil Yakupov
Shamil Yakupov

Reputation: 5469

Existance in JSON field check query

I'm using PostgreSQL JSON type field in my database.

JSON format:

[
   {"id": "13", "something": "not_important"},
   {"id": "5426565165191919", "something": "not_important"},
   {"id": "46", "something": "not_important"}
   ...
]

List of IDs like ["13", "46", "84"].

How to make query that tell me that some ID is not exists? (Not important which one.)

Upvotes: 0

Views: 1424

Answers (2)

bgoldst
bgoldst

Reputation: 35314

You can do this by left-joining your "expected" ids against the "actual" ids, and then filtering for rows in the result-set where the "actual" ids table failed to join. The only trick is extracting from the JSON the integral id values. This is how it could be done, assuming you are hard-coding all ids right in the SQL:

select
    expected.id
from
    (select substring(value::varchar from 2 for char_length(value::varchar)-2)::bigint id from json_array_elements('["13","46","84"]'::json)) expected
    left join (select (value->>'id')::bigint id from json_array_elements('[{"id":"13"},{"id":"5426565165191919"},{"id":"46"}]'::json)) actual on actual.id=expected.id
where
    actual.id is null
;

In the above query, I'm calling json_array_elements() to extract the individual array elements from each JSON string. Because the target JSON string has object array elements, we must further extract the "id" values, which can be done with the -> operator. Note that value seems to be the default name assigned to the single column output by the json_array_elements() function.

A quirk is that the extracted JSON (i.e. the value value) is still of type json, and it seems you can't cast json text directly to an integral value such as int or bigint, even if the entire json text consists only of a valid int/bigint, but you can go through varchar to get there:

select '34'::json::bigint; -- "ERROR:  cannot cast type json to bigint"
select '34'::json::varchar::bigint; -- works, 34

That's why I had to cast the json values with ::varchar::bigint.

Also, I had to extract the expected ids from their double-quoted strings using substring(), as the ::bigint cast would otherwise not work.

Also, minor point, I used bigint and not int because you've got a rather large number there (5426565165191919).

If you want to just select a boolean value indicating if at least one expected id is missing, you can change the select clause to read:

select
    case when count(expected.id)>0 then true else false end some_missing

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657182

WITH t(data) AS (
   VALUES ('           [
   {"id": "13", "something": "not_important"},
   {"id": "5426565165191919", "something": "not_important"},
   {"id": "46", "something": "not_important"}
   ]'::json)
   )
SELECT NOT EXISTS (
   SELECT data::text, o::text  -- cast to text for display in fiddle
   FROM   t, json_array_elements(t.data) AS d(o)
   RIGHT  JOIN unnest('{13,46,84}'::bigint[]) u(id) ON u.id = (d.o->>'id')::bigint
   WHERE  d.o IS NULL
   ) AS all_found;
  • The CTE (WITH query) just substitutes for an actual table t.
  • Using LEFT JOIN / NOT NULL to identify missing values. Details:
  • Assuming that bigint is wide enough to hold your id values. Else, switch to numeric.

SQL Fiddle.

More explanation in this related answer:

Upvotes: 2

Related Questions