PostgreSQL JSONB 檢查 json 裡面資料是否存在

若要查詢一個資料裡面是否存在key,可以使用以下方法

CREATE OR REPLACE FUNCTION key_exists(some_json jsonb, outer_key text)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (some_json#>>outer_key::text[]) IS NOT NULL;
END;
$$ LANGUAGE plpgsql;

use case:

SELECT key_exists('{"A":{"B":"C"}}'::jsonb,'{A,B}'); -- TRUE
SELECT key_exists('{"A":{"B":"C"}}'::jsonb,'{A}'); -- TRUE
SELECT key_exists('{"A":{"B":"C"}}'::jsonb,'{C}'); -- FALSE

Reference:
http://stackoverflow.com/questions/28921355/how-do-i-check-if-a-json-key-exists-in-postgres
http://www.postgresql.org/docs/devel/static/functions-json.html
http://schinckel.net/2014/05/25/querying-json-in-postgres/
http://stackoverflow.com/questions/19422233/check-if-field-exists-in-json-type-column-postgresql