Postgres – Как анализировать элементы массива JSON?
PostgreSQL 15.4
Ниже представлена таблица postgres table1:
CREATE TABLE table1 (
id INT PRIMARY KEY,
name TEXT,
skills JSON
);
со вставленными ниже строками:
INSERT INTO table1 (id, name, skills) VALUES
(1, 'Alice', ‘[
{“sid" : 11, "description" : “Cardio"},
{"sid" : 13, "description" : “Anesthist"}
]'
),
(2, ‘Bob', ‘[
{“sid" : 10, "description" : “Gastro"},
{"sid" : 9, "description" : “Oncology"}
]’
),
(3, ‘Sam', ‘[
]’
);
Ниже приведен желаемый результат после выполнения запроса select:
id name skill
---------------------
1 Alice [“Cardio”,“Anestisht”]
2 Bob ["Gastro","Oncology"]
3 Sam []
где столбец skill — это тип TEXT
Попробовал ниже запрос
select
id, name, d ->> 'description' as skill
from table1,
json_array_elements(skills) as d
это дает повторяющиеся строки (как показано ниже) с отсутствующей строкой, что неверно.
id name skill
---------------------
1 Alice Cardio
1 Alice Anestisht
2 Bob Gastro
2 Bob Oncology
Ответы 2
Используйте array_agg(), чтобы объединить описания в массив:
select
id,
name,
array_agg(skill ->> 'description') as skill
from table1, json_array_elements(skills) AS skill
group by 1, 2
Смотрите живую демонстрацию.
Чтобы вернуть строки для людей без навыков, есть один из способов:
id,
name,
array_agg(skill ->> 'description')
from table1, json_array_elements(skills) AS skill
group by 1, 2
union all
select
id,
name,
array[]::text[]
from table1
where json_array_length(skills) = 0
Смотрите живую демонстрацию.
@overexchange См. отредактированный ответ о том, как включить неквалифицированных людей.
Третий столбец (skill) в первом запросе SELECT имеет тип _text Согласно демонстрационному коду здесь, значение третьего столбца в запросе SELECT говорит NULL, но желаемый результат — []. Пробовал '[]'::_text вместо null во втором запросе SELECT, но безуспешно.
@overexchange Изменено на возврат пустого массива при отсутствии навыков. Ссылка на демо-версию также обновлена.
Как order by назвать эту комбинацию select запроса?
@overexchange Вы должны заключить все это в круглые скобки, чтобы убедиться, что order by, который вы добавляете в конце, применим ко всему, а не только ко второму из двух unioned select: демо.
@overexchange да: select * from ( <query in answer> ) x order by name (x дает псевдоним внутреннему запросу, который является синтаксическим требованием)
Итак, мы видим ошибку в клиентском коде: pq unable to parse array expected '{' at offset 0. Как получить обычные массивы SQL с фигурной скобкой {} для устранения этой ошибки? Потому что для skills мы получаем результат: [[1 2 3][1 2 3][1 2 3]] с квадратным разветвителем, но без фигурной скобки {
«Обычные массивы SQL» используют квадратную, а не фигурную скобку. Пожалуйста, объясните, почему вы считаете, что в массиве следует использовать фигурные скобки.
Используйте jsonb_path_query_array(). Он получает массив jsonb, как показано в ожидаемом результате, а не обычный массив SQL text.
демо на db<>fiddle
select
id,
name,
jsonb_path_query_array(skills::jsonb,'$[*].description') as skill
from table1;
Если вы используете более старую версию PostgreSQL (версии 9.3-11 ), вы можете развернуть и свернуть массив с помощью скалярного подзапроса , а затем объединить()null в пустой массив:
select
id,
name,
coalesce((select json_agg(e->>'description')from json_array_elements(skills)e)
,'[]') as skill
from table1;
Что касается тестирования, я тестировал count(*) на table1 по сравнению с count(*) на select выводе запроса. Как проверить значения первых двух столбцов в table1 по сравнению со значениями первых двух столбцов в выводе запроса select?
@overexchange Можете ли вы показать пример, как это будет выглядеть? Я не уверен, что следую. Звучит так, будто вы описываете объединение, но я сомневаюсь, что вы это имеете в виду: select * from table1 as t1 full join (the_select_statement) as t2 using(id,name);
да, это то, что я имел в виду
Я пробовал работать с пустым
skills, как показано здесь, не работает. Желаемый результат — 3 строки, но он показывает две строки.