Как вернуть массив jsonb и массив объектов из моих данных?

Обратите внимание, что есть другие ключи, кроме "sales_tv" и "sales_radio". Для запросов ниже мне просто нужно сосредоточиться на "sales_tv" и "sales_radio".

Мне нужно найти все продажи для Jim на 2012 год. Все, что начинается с "sales_", а затем помещает это в объект (просто нужно, чтобы проданный продукт и стоимость). например:.

Но я не могу даже получить только данные Джима. Вместо этого я получаю:

Ответы

Ответ 1

Вы обрабатываете результат первого соединения как JSON, а не как текстовую строку, поэтому используйте jsonb_each() вместо jsonb_each_text():

SELECT t.employee, json_object_agg(a.k, d.value) AS sales
FROM   mytable t
JOIN   LATERAL jsonb_each(t.data) a(k,v) ON a.k LIKE 'sales_%'
JOIN   LATERAL jsonb_to_recordset(a.v) d(yr text, value float) ON d.yr = '2012'
WHERE  t.employee = 'Jim'  -- works because employee is unique
GROUP  BY 1;

GROUP BY 1 является сокращением для GROUP BY t.employee.
Результат:

employee | sales
---------+--------
Jim      | '{ "sales_tv" : 40, "sales_radio" : 76 }'

Я также распутал и упростил ваш запрос.

json_object_agg() играет важную роль в объединении пар имя/значение в качестве объекта JSON. При необходимости используйте jsonb, если вам это нужно, или используйте jsonb_object_agg() в Postgres 9.5 или новее.

Использование явного синтаксиса JOIN для подключения условий в их наиболее очевидном месте.
Тот же без явного синтаксиса JOIN:

SELECT t.employee, json_object_agg(a.k, d.value) AS sales
FROM   mytable t
     , jsonb_each(t.data)      a(k,v) 
     , jsonb_to_recordset(a.v) d(yr text, value float)
WHERE  t.employee = 'Jim'
AND    a.k LIKE 'sales_%'
AND    d.yr = '2012'
GROUP  BY 1;

Ответ 2

Ваш первый запрос можно решить следующим образом (стрельба из бедра, без доступа к PG 9.4 здесь):

SELECT employee, json_object_agg(key, sales)::jsonb AS sales_
FROM (
  SELECT t.employee, j.key, sum((e->>'value')::int) AS sales
  FROM mytable t,
       jsonb_each(t.data) j,
       jsonb_array_elements(j.value) e
  WHERE t.employee = 'Jim'
    AND j.key like 'sales_%'
    AND e->>'yr' = '2012'
  GROUP BY t.employee, j.key) sub
GROUP BY employee;

Трюк заключается в том, что вы используете LATERAL объединения для "отслаивания" внешних слоев объекта jsonb, чтобы глубже проникать в данные. Этот запрос предполагает, что у Джима могут быть продажи в нескольких местах.

(Работа над вашим запросом 2)