Postgresql Преобразование объекта Json в столбцы

Я использую базу данных 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 jsonb postgresql-11
person Emrullah    schedule 27.10.2020    source источник
comment
Откуда вы знаете, что question1field относится к первому элементу test_question_number?   -  person Philipp Johannis    schedule 27.10.2020
comment
Я не знал, что вы имеете в виду. Это данные json, и они последовательные   -  person Emrullah    schedule 27.10.2020
comment
Это недопустимый json из-за "test_question_number": {"1000000000", "5000000000"}, — он будет действителен с ["1000000000", "5000000000"]. Вы можете проверить пример, например, с помощью jsonlint.com.   -  person Philipp Johannis    schedule 27.10.2020
comment
Да, спасибо за поправку. Я отредактирую вопрос.   -  person Emrullah    schedule 27.10.2020

Ответы (1)


arrow_upward
0
arrow_downward

Статическое поворотное решение может быть

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

Демо

person Barbaros Özhan    schedule 29.10.2020
comment
Спасибо, Барбарос, но это не то, что я ищу. Мне нужно динамическое решение. - person Emrullah; 31.10.2020
comment
Пожалуйста, @Emrullah, но его очень сложно преобразовать в динамический, даже с помощью некоторых функций, возвращающих record или refcursor. - person Barbaros Özhan; 31.10.2020