Блог

Главная - Вопросы по программированию - POSTGRESQL: как запросить вложенный объект JSONB, чтобы получить отфильтрованный объект JSONB?

POSTGRESQL: как запросить вложенный объект JSONB, чтобы получить отфильтрованный объект JSONB?

#sql #postgresql #jsonb

#sql #postgresql #jsonb

Вопрос:

Введите Json

 {
"orderId": "order1",
"fulfillerId":"ful1",
"orderDetailsUrl":"het",
"items":[{
        "decorationTechnology":"laserEngraving",
        "itemId":"item1",
        "productName":"Test Sku for Oracle testing"
    },
    {
        "decorationTechnology":"laserEngraving",
        "itemId":"item2",
        "productName":"Test Sku for Oracle testing"
    }
  ]
}

Ожидаемый результат JSON

 {
   "fulfillerIds":[
      "ful1"
   ],
   "orderIds":[
      "order1"
   ],
   "itemIds":[
       "item1",
        "item2"
   ]
}

Необходимо сформировать выше ожидаемого JSON пытался использовать row_to_json() и jsonb_array_elements(), но не смог получить ожидаемый результат

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

1. Какую версию Postgres вы используете?

2. версия 11.8 @a_horse_with_no_name

Ответ №1:

Если вы используете Postgres 12 или более позднюю версию, вы можете использовать запросы пути SQL / JSON:

 select jsonb_build_object(
         'orderIds', jsonb_path_query_array(the_column, '$.orderId'), 
         'fulfillerIds', jsonb_path_query_array(the_column, '$.fulfillerId'),
         'itemIds', jsonb_path_query_array(the_column, '$.items[*].itemId')
       ) as filtered
from the_table;

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

1. Поскольку я использую версию 11.8, это может не сработать для меня @a_horse_with_no_name

Ответ №2:

В более ранних версиях он работает с конкатенацией и построением массивов:

 WITH t1 AS
    (
        SELECT '{ "orderId": "order1", "fulfillerId":"ful1", "orderDetailsUrl":"het", "items":[{ "decorationTechnology":"laserEngraving", "itemId":"item1", "productName":"Test Sku for Oracle testing" }, { "decorationTechnology":"laserEngraving", "itemId":"item2", "productName":"Test Sku for Oracle testing" } ] } '::jsonb AS field
    )
SELECT jsonb_build_object('orderId', json_build_array (field->>'orderId')) || jsonb_build_object('fulfillerId', json_build_array (field->>'fulfillerId')) || jsonb_build_object('orderDetailsUrl', json_build_array (field->>'orderDetailsUrl')) AS jsonb_result
FROM t1
;