Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

drop table if exists employees;
create table employees(id int primary key, surname text, first_name text, middle_name text);
insert into employees values 
(5680, 'James', 'Julia', 'Jane'),
(5681, 'Adam', 'Ada', 'Adela');
table employees;

DROP TABLE

CREATE TABLE

INSERT 0 2

id
surname
first_name
middle_name

5680
James
Julia
Jane

5681
Adam
Ada
Adela

SELECT 2

-- create json for each id separately with arrays
select
	jsonb_build_object(
		'EmpNames',
		jsonb_build_array(
			jsonb_build_object(
				'id', id,
				'surname', surname,
				'first_name', first_name,
				'middle_name', middle_name
			)
		)
	)
from employees

jsonb_build_object

{"EmpNames": [{"id": 5680, "surname": "James", "first_name": "Julia", "middle_name": "Jane"}]}

{"EmpNames": [{"id": 5681, "surname": "Adam", "first_name": "Ada", "middle_name": "Adela"}]}

SELECT 2

-- create json for each id separately, simplified variant
select
	jsonb_build_object(
		'EmpNames',
		jsonb_build_object(
			'id', id,
			'surname', surname,
			'first_name', first_name,
			'middle_name', middle_name
		)
	)
from employees

jsonb_build_object

{"EmpNames": {"id": 5680, "surname": "James", "first_name": "Julia", "middle_name": "Jane"}}

{"EmpNames": {"id": 5681, "surname": "Adam", "first_name": "Ada", "middle_name": "Adela"}}

SELECT 2