Как получить JSON объект из PostgreSQL?

Question

[hbrmdc]

PostgreSQL 9.4
Как получить данные вот в таком виде:

parentTableFirstProp: 'string',
parentToChildReference: [
    {childTableFirstProp: 'another string'},
    {childTableFirstProp: 'yet another string'}
}]

вместо такого:

[{
    parentTableFirstProp: 'string',
    childTableFirstProp: 'another string',
},{
    parentTableFirstProp: 'string',
    childTableFirstProp: 'yet another string'
}]

Возможно ли это сделать каким-то простым запросом или каждый раз нужно делать по 2+ SELECT и затем вкладывать один ответ в другой используя алиасы?
Благодарю вас за уделенное время!

Answer 1

[hbrmdc]

select row_to_json(t)
from (
select "ParentTable"."parentTableFirstProp", (
select array_to_json(array_agg(row_to_json(child)))
  from (
    select "childTableFirstProp"
    from "ChildTable"
    where "ChildTable"."id"="ParentTable"."parentToChildReference"
  ) child

  ) as parentToChildReference
from "ParentTable"
) t

Answer 2

[Кирилл]

Как-то так:

select row_to_json(j.*) from (

	select  'string' as parentTableFirstProp, to_json(

		(
		select to_json(array_agg(r.*)) from (
			select s as childTableFirstProp from (select unnest('{another string,yet another string}'::text[]) as s) _
		)as r
		)
	) as parentToChildReference

) as j

Comments

[hbrmdc]

почему вот это в коде: {another string,yet another string} ?

[Кирилл]

hbrmdc: это массив он нужен чтоб получить 2 строки в селекте, реальной таблицы у меня нет, я её и делать не стал, ленивый)

Answer 3

[romy4]

сверху не по стандарту json, так что вам postgres всё верно возвращает. переделайте, чтобы всё воспринималось по стандарту

Comments

[hbrmdc]

да не в том дело - я вообще navicat gui пользую. Я получаю отдельные строки вместо json объекта с вложенностями

[hbrmdc]

то есть, если ParentTable имеет строку, в которой конкретный столбец имеет связь с двумя строками другой таблицы, то я получаю две строки, в каждой из которых инфа из ParentTable + инфа из одной из связанных строк, вместо того, чтобы получить один json объект, в котором связанные строки были бы внутри одного объекта (строки из родительской таблицы)

[romy4]

складывать json строки -не задача postgres, хотя вы можете реализовать такое. можете как-то ухитриться используя json_object(text[]), типа одно и второе преобразовав в текст, затем через функцию
www.postgresql.org/docs/current/static/functions-j...

[hbrmdc]

romy4: Спасибо, это тот ответ, который мне был нужен. Если я вас правильно понял, то на каждую таблицу нужно делать отдельный select, а затем с помощью алиасов складывать json объект со всеми желаемыми вложенностями. Кроме того, для этого стоит использовать VIEWS. Верно? Я только начал изучать Postgres и sql в целом, хочу избежать как можно больше детских ошибок на старте) Можете привести пример самой обычной команды для получения json объекта в том виде, о котором я писал?

[romy4]

hbrmdc: если, как вы говорите, общий ключ, то через join а два поля с двух таблиц уже преобразовывать.
и да, изучите join - без него как без рук