sqltojson.sql

CREATE TABLE  IF NOT EXISTS media(
	id SERIAL PRIMARY KEY,
	label VARCHAR(256),
	type media_type,
	"companyCode" VARCHAR(100),
	"companyId" INT
);

CREATE TABLE  IF NOT EXISTS media_file(
	id SERIAL PRIMARY KEY,
	height smallint CHECK (height >= 0),
	width smallint CHECK (width >= 0),
	"exibitionTime" smallint DEFAULT 5000,
	sequence smallint CHECK(sequence >= 0),
	img BYTEA,
	mimetype VARCHAR(15),
	media INT,
	FOREIGN KEY (media) REFERENCES media(id) ON DELETE CASCADE
);

--a query está aqui
SELECT m."id", m."label", m."type", m."companyCode", m."companyId",
        COALESCE((
          SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(d)))
      FROM (
        SELECT md."id", md."label", md."type",
        COALESCE((
          SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(f)))
          FROM (
          SELECT mf."id",mf."height", mf."width", mf."sequence", mf."exibitionTime", mf.mimetype, ENCODE(mf.img,'base64') as img
          FROM media_file mf
          WHERE mf.media = md.id
          ) f
        ),'[]') AS files
        FROM media md
        WHERE md.id=m.id
      ) d
    ),'[]') AS media
         FROM media m WHERE m."companyId"=$1