PostgreSQL. Формирование json и работа с ним.

В заметке приведён пример формирования json средствами sql в PostgreSQL и как можно делать выборки из полей типа json.

На таблицах emp и dept (скрипты создания таблиц и их наполнения можно взять здесь) будем формировать поле типа json такого вида.

Для этого нам понадобятся 2 функции: json_build_object и json_agg. Код решения, создающего таблицу с полем типа json требуемого формата ниже

create table scott.dept_emp_json as (
with emp as (
--собираем инфо о работниках из таблицы emp в JSON-массив
select 
deptno
,json_agg (json_build_object(
'empno', empno
, 'ename', ename
, 'job', job
, 'hiredate', hiredate)) emp_json_array
from scott.emp
group by 1)
--собираем итоговый json
select d.deptno
,json_build_object(
'deptno', d.deptno,
'dname', d.dname,
'loc', d.loc,
'emp_data', emp_json_array
'empty_json_array', '[]'::json -- просто пример формирования пустового массива в жейсоне
) json_data
from scott.dept d
join emp e
on d.deptno = e.deptno
)

Задача по формированию решена, теперь поселектим полученный json.

Выберем все локации

select json_data -> 'loc' as loc
from scott.dept_emp_json
--NEW YORK
--DALLAS
--CHICAGO

Получим список должностей работников (реализация select distinct job from scott.emp). Получилось немножко сложнее, чем в предыдущем примере. Кавычки в финальной выборке специально оставил.

with job_list as (
select (json_array_elements((json_data ->> 'emp_data')::json) -> 'job')::text as job
from scott.dept_emp_json)
select distinct job
from job_list