Блог

Главная - Вопросы по программированию - Сокращение строк в пару ключ / значение json с помощью postgres

Сокращение строк в пару ключ / значение json с помощью postgres

#javascript #json #postgresql

#javascript #json #postgresql

Вопрос:

У меня есть таблица postgres примерно так.

  ---- ----------- ---------------------- -------- 
| id |    Key    |        Value         | userId |
 ---- ----------- ---------------------- -------- 
|  1 | email     | thomas@reggi.com     |      1 |
|  2 | firstName | thomas               |      1 |
|  3 | lastName  | reggi                |      1 |
|  4 | email     | new.thomas@reggi.com |      1 |
 ---- ----------- ---------------------- --------

Я ищу способ «уменьшить» эту таблицу до объекта json.

 {
   "email": "new.thomas@reggi.com",
   "firstName": "thomas",
   "lastName": "reggi"
}

Насколько близко я могу подойти к этому, просто используя postgres?

Ответ №1:

Если вызывается таблица data , попробуйте это ( jsonb_pretty только для целей отображения):

 SELECT jsonb_pretty(
          jsonb_object_agg(key, value ORDER BY id)
       )
FROM data
WHERE userid = 1;

┌──────────────────────────────────────┐
│             jsonb_pretty             │
├──────────────────────────────────────┤
│ {                                   ↵│
│     "email": "new.thomas@reggi.com",↵│
│     "lastName": "reggi",            ↵│
│     "firstName": "thomas"           ↵│
│ }                                    │
└──────────────────────────────────────┘
(1 row)

Это зависит от функции, которая jsonb не сохраняет дубликаты ключей.

Он также основан на том факте, что jsonb всегда будет сохраняться последняя добавленная пара ключ / значение.

Комментарии:

1. Можно управлять, какие ключи хранить: jsonb_object_agg (как и любая другая агрегатная функция) принимает ORDER BY предложение, например. jsonb_object_agg(key, value ORDER BY id) чтобы сохранить значение key с наивысшим id значением (последняя добавленная пара ключ-значение будет сохранена)

2. Да, но это означает, что вам нужно полагаться на недокументированную деталь реализации jsonb , а именно, что последняя добавленная пара будет сохранена. Может быть, я слишком беспокоюсь. Я добавлю несколько идей к ответу, чтобы решить проблему.

3. Это задокументировано : если во входных данных указаны повторяющиеся ключи, сохраняется только последнее значение. — даже json тип имеет аналогичную обработку: если объект JSON внутри значения содержит один и тот же ключ более одного раза, все пары ключ / значение сохраняются. (Функции обработки рассматривают последнее значение как рабочее.)

4. Отлично, спасибо. Я улучшил ответ в соответствии с этим.

Ответ №2:

Если вы хотите всегда иметь последнее значение для ключа, вы можете использовать a CTE и функцию RANK() window:

 SELECT * FROM p;
┌────┬───────────┬──────────────────────┬────────┬────────────────────────────┐
│ id │    key    │        value         │ userid │     modification_time      │
├────┼───────────┼──────────────────────┼────────┼────────────────────────────┤
│  1 │ email     │ thomas@reggi.com     │      1 │ 2016-10-05 12:53:32.936704 │
│  2 │ firstName │ thomas               │      1 │ 2016-10-05 12:53:32.936704 │
│  3 │ lastName  │ reggi                │      1 │ 2016-10-05 12:53:32.936704 │
│  4 │ email     │ new.thomas@reggi.com │      1 │ 2016-11-06 15:53:48.025775 │
└────┴───────────┴──────────────────────┴────────┴────────────────────────────┘
(4 rows)
  
 WITH info_with_rank_for_user AS (
  SELECT userId,
         modification_time,
         value,
         key,
         RANK() OVER (PARTITION BY userId, key ORDER BY id DESC)
  FROM p
)
SELECT userId,
       json_object_agg(key, value),
       MAX(modification_time) AS last_settings_modification_time
FROM info_with_rank_for_user
WHERE rank = 1
GROUP BY userId
;
┌────────┬────────────────────────────────────────────────────────────────────────────────────┬─────────────────────────────────┐
│ userid │                                  json_object_agg                                   │ last_settings_modification_time │
├────────┼────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────┤
│      1 │ { "email" : "new.thomas@reggi.com", "firstName" : "thomas", "lastName" : "reggi" } │ 2016-11-06 15:53:48.025775      │
└────────┴────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────┘
(1 row)

Комментарии:

1. Это идеально! У меня есть еще один столбец в моей таблице, есть идеи, как это сделать? Итак, столбцы заполнены userId, otherColumn, json_object_agg ?

2. @ThomasReggi: Это зависит от того, что otherColumn представляет. Я добавил пример, в котором это также то, что вы хотите объединить (здесь используется MAX() ). Если это не так, добавьте это в свой вопрос, иначе сложно помочь 🙂