Postgresql преобразует объект Json в столбцы

#postgresql

Вопрос:

Я использую базу данных 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.

Теперь я хотел бы преобразовать его в colum и row. Как показано ниже:

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

Я пробовал jsonb_build_object, jsonb_populate_recordset и некоторые функции, но я не смог решить.

1. Откуда вы знаете, что question1 это поле относится к первому элементу test_question_number ?

2. Я не знал, что вы имеете в виду. Это данные json, и они являются последовательными

3. Это недопустимый json из-за "test_question_number": {"1000000000", "5000000000"}, — это было бы допустимо с ["1000000000", "5000000000"] . Вы можете проверить пример, т.Е. с помощью jsonlint.com

4. Да, спасибо за исправление. Я отредактирую вопрос.

Ответ №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

1. Спасибо Barbaros, но это не то, что я ищу. Мне нужно динамическое решение.

2. добро пожаловать, @Emrullah , но его очень сложно преобразовать в динамический, даже с помощью некоторых функций, возвращающих record or refcursor .