postgresql - Postgresql 将 Json 对象转换为列

Question

我正在使用 postgresql 数据库。我有一列是jsonb 数据类型。例如，我有一个如下的 json 数据：

{
"test_question_number": ["1000000000", "5000000000"],
"question1": 0.04975124378109453,
"question2": 5.077114427860696,
"question3": 75621.89054726369,
"question4": 3482.587064676617,
"question6": 1,
"question8": 0.000176068
}

如您所见，它是键值 json 数据。并且数据可以不同，所以其他保存的 json 数据的键名不同。

现在我想将它转换为列和行。如下所示：

---------------------------------------------------------------------------------------
|   |test_question_number  |question1|           |question2|         |question3| 
---------------------------------------------------------------------------------------
| 1 |   "1000000000"       | 0.04975124378109453| 5.077114427860696  |75621.89054726369
------------------------ --------------------------------------------------------------
| 2 |   "5000000000"       |                    |                    |
---------------------------------------------------------------------------------------

我尝试了 jsonb_build_object、jsonb_populate_recordset 和一些函数，但我无法解决。

标签： postgresql

Answer 1

静态旋转解决方案可能是

WITH t AS
(
  SELECT JSONB_TYPEOF(value::JSONB) AS type, js.*
    FROM t
   CROSS JOIN JSONB_EACH(jsdata) AS js
)
SELECT arr.*, question1, question2, question3, question4, question6, question8
  FROM
  (
   SELECT row_id, test_question_number 
     FROM t
    CROSS JOIN JSONB_ARRAY_ELEMENTS(value::JSONB) 
     WITH ORDINALITY arr(test_question_number,row_id)
    WHERE type = 'array' ) AS arr
  LEFT JOIN
  ( SELECT cnt, MAX(value::text) FILTER (WHERE key = 'question1') AS question1,
                MAX(value::text) FILTER (WHERE key = 'question2') AS question2,
                MAX(value::text) FILTER (WHERE key = 'question3') AS question3,
                MAX(value::text) FILTER (WHERE key = 'question4') AS question4,
                MAX(value::text) FILTER (WHERE key = 'question6') AS question6,
                MAX(value::text) FILTER (WHERE key = 'question8') AS question8
      FROM (SELECT t.*, COUNT(*) OVER (PARTITION BY key) AS cnt
              FROM t
             WHERE type != 'array' 
           ) AS q
     GROUP BY cnt ) AS obj
         ON arr.row_id = obj.cnt 

按类型将元素区分为 JSON 对象，无论array是non-array

Demo