Início / dba / Perguntas / 311160
Accepted
persson
Asked: 2022-04-21 06:29:26 +0800 CST

Junte o número variável de linhas no Postgres no array json

  • 772

Tenho a seguinte tabela:

timestamp | type | value | source
----------+------+-------+--------
2020-01-01| 1    | 10    | 2
2020-01-01| 2    | 20    | 2
2020-01-02| 1    | 5     | 4
2020-01-02| 2    | 6     | 3
2020-01-02| 5    | 4     | 3
2020-01-02| 4    | 8     | 1

Cada par timestamp+ é único. typePara cada timestamp, pode haver um número variável de linhas (mas cada uma com um type). valuee sourcesão os dados reais.

Agora eu gostaria de agregar timestampem um array json onde, para cada timestamp, as tuplas disponíveis ( type, value, ) são incluídas. sourceIdealmente, algo como:

[
  {
    "2020-01-01": [{"type": 1, "value": 10, "source": 2}, {"type": 2, "value": 20, "source": 2}]
  },
  {
    "2020-01-02": [{"type": 1, "value": 5, "source": 4}, {"type": 2, "value": 6, "source": 3}, {"type": 5, "value": 4, "source": 3}, {"type": 4, "value": 8, "source": 1}]
  }
]

Eu realmente não tenho preferências fortes no formato de saída, desde que as informações estejam lá e agrupadas corretamente, então, por exemplo, isso também seria bom:

[
  {
    "timestamp": "2020-01-01", "data": [{"type": 1, "value": 10, "source": 2}, {"type": 2, "value": 20, "source": 2}]
  },
  {
    "timestamp": "2020-01-02", "data": [{"type": 1, "value": 5, "source": 4}, {"type": 2, "value": 6, "source": 3}, {"type": 5, "value": 4, "source": 3}, {"type": 4, "value": 8, "source": 1}]
  }
]

Ou isso (que eu acho que exigiria a conversão typepara string, o que não é um problema):

[
  {
    "timestamp": "2020-01-01", "1": {"value": 10, "source": 2}, "2": {"value": 20, "source": 2}
  },
  {
    "timestamp": "2020-01-02", "1": {"value": 5, "source": 4}, "2": {"value": 6, "source": 3}, "5": {"value": 4, "source": 3}, "4": {"value": 8, "source": 1}
  }
]

Mesmo isso, desde que a ordenação do campo seja conhecida com antecedência:

[
  {
    "2020-01-01": [[1, 10, 2], [2, 20, 2]]
  },
  {
    "2020-01-02": [[1, 5, 4], [2, 6, 3], [5, 4, 3], [4, 8, 1]]
  }
]

Em suma, o que for mais fácil/eficiente.

postgresql json

1 respostas

  1. Best Answer
    WITH cte AS (
    SELECT "timestamp", json_agg(json_build_object('type', "type", 
                                                   'value', "value",
                                                   'source', "source")) intermediate_json
    FROM test
    GROUP BY 1
)
SELECT json_agg(json_build_object("timestamp", intermediate_json)) final_json
FROM cte

    https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2fc37233058437189b8e0a8eea05a01b

    • 1

relate perguntas