Postgres 9.5 Запрос соответствия JSON Property в массиве

Question

У меня есть адрес столбца, введите jsonb[]Внутри таблицы Houses есть массив, который выглядит примерно так:

"{
  "{\"zip\": \"13203-1807\", 
  \"city\": \"\SYRACUSE\", 
  \"state\": \"NEW YORK\", 
  \"street\": \"\999 PROSPECT AVENUE\"}"
 }"

Я пытаюсь запросить все Дома, где Адрес Город "Сиракузы"; пока что у меня есть:

SELECT * FROM Houses WHERE address -> 'city' = 'SYRACUSE'

И получите эту ошибку:

No operator matches the given name and argument type(s). You might need to add explicit type casts.

Я просмотрел официальную документацию PG по функциям и операторам JSON, а также несколько ответов на Stackru безрезультатно.

Answer 1

Итак, как уже упоминалось в комментариях выше, вы пытаетесь использовать оператор JSON в массиве postgresql, поэтому вы получаете ошибку типа. Есть несколько способов приблизиться к этому, в зависимости от того, что вы действительно хотите сохранить в этом поле.

Для этих примеров я собираюсь принять следующую таблицу:

CREATE TABLE houses (id INT, address <some datatype>);

Если вы хотите, чтобы это был массив postgres jsonbВы должны распаковать массив, который вы можете сделать так:

WITH all_addresses AS (
  SELECT id, unnest(address) as add
    FROM houses)
SELECT *
  FROM houses h
  WHERE
    id IN (SELECT id
             FROM all_addresses
             WHERE add->'city' = to_jsonb('SYRACUSE'::text));

Если, с другой стороны, вы хотите использовать массив JSON, тогда запрос может выглядеть следующим образом (очень похоже, так как вам все еще нужно раскрутить массив):

WITH all_addresses AS (
  SELECT id, jsonb_array_elements(address) as add
    FROM houses)
SELECT *
  FROM houses h
  WHERE
    id IN (SELECT id
             FROM all_addresses
             WHERE add->'city' = to_jsonb('SYRACUSE'::text));

Я не могу точно сказать по вашему вопросу, но возможно, что вы когда-либо сохраняете только один адрес на строку в этом столбце (на основе данных вашего примера и того факта, что столбец называется "адрес", а не "адреса"), Если это так, ваш запрос становится намного проще:

SELECT *
  FROM houses
  WHERE address->'city' = to_jsonb('SYRACUSE'::text);