Check if an object key appears twice in a json array of objects

Use jsonb_array_elements(), e.g.:

with data(js) as (
    select 
        '[
        { "type": "foo", "desc": "example" },
        { "type": "foo", "desc": "second example" },
        { "type": "bar", "desc": "third example" }
        ]'::jsonb
)
select elem->>'type' as "type", count(elem->'type')
from data, jsonb_array_elements(js) elem
group by 1;

 type | count 
------+-------
 foo  |     2
 bar  |     1
(2 rows)    

The function should look like this:

create or replace function check_duplicates(source jsonb, key text)
returns boolean language sql as $$
    select max(count) > 1
    from (
        select count(elem->key)
        from jsonb_array_elements(source) elem
        group by elem->key
        ) s
$$;

Usage:

with data(js) as (
    select 
        '[
        { "type": "foo", "desc": "example" },
        { "type": "foo", "desc": "second example" },
        { "type": "bar", "desc": "third example" }
        ]'::jsonb
)
select check_duplicates(js, 'type')
from data;

 check_duplicates 
------------------
 t
(1 row) 

Here's a function that does that.

CREATE OR REPLACE FUNCTION more_than_two_foos(s jsonb) RETURNS bool AS $$
DECLARE
    c integer;
BEGIN
    SELECT count(*)
    FROM (
        SELECT 1
        FROM jsonb_array_elements(s)
        WHERE value->>'type'='foo'
        LIMIT 2
    ) t
    INTO c;
    RETURN c>=2;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

And here's an some examples:

$ SELECT more_than_two_foos('[
  { "type": "foo", "desc": "example" },
  { "type": "foo", "desc": "second example" },
  { "type": "bar", "desc": "third example" }
]');
 more_than_two_foos 
--------------------
 t
(1 row)

$ SELECT more_than_two_foos('[
  { "type": "foo", "desc": "second example" },
  { "type": "bar", "desc": "third example" }
]');
 more_than_two_foos 
--------------------
 f
(1 row)

The idea is that it goes through the elements of the jsonb array using jsonb_array_elements and counts the elements that have type equal to foo.