json - 如何从具有 json 列条件的表中进行选择？

Question

我在 PostreSQL 9.6 中有一个表 contact_information。

CREATE TABLE contact_information
(
  employee_id UUID NOT NULL PRIMARY KEY,
  messengers JSON NOT NULL
);

INSERT INTO contact_information 
  (employee_id, messengers) 
VALUES 
  ('8b5fbfec-d213-426c-86fa-4fda5f586319', '[{"Type":"skype","IDs":["some skype id","another skype id"]}]');

我需要选择 ID 包含子字符串的所有 employee_id，例如“id ILIKE '%other%'”。

我尝试了什么:

SELECT
  ci.employee_id,
  json_array_elements(json_array_elements(ci.messangers)->'IDs'::text)::text AS ids
FROM contact_information AS ci

得到:

3ba6feba-ff81-11e4-9408-984be16afda1    "some skype id"
3ba6feba-ff81-11e4-9408-984be16afda1    "another skype id"

但我无法添加到语句 WHERE ids ILIKE '%other%' - 列 ids 不存在。

如果我在努力

SELECT
  ci.employee_id
FROM contact_information AS ci
WHERE json_array_elements(json_array_elements(ci.messangers)->'IDs'::text)::text ILIKE '%other%'

得到:错误:WHERE 中不允许设置返回函数。

有解决这个问题的想法吗？

Answer 1

已解决:

SELECT DISTINCT t.employee_id 
FROM (
  SELECT
    ci.employee_id,
    json_array_elements(json_array_elements(ci.messangers)->'IDs'::text)::text AS ids
  FROM contact_information AS ci
) as t
WHERE t.ids ILIKE '%other%'

抱歉问了一个愚蠢的问题 =(