PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Extract values from jsonb column with dynamic keys

I have a column of a table with this kind of structure

{
    "Warehouse1": {
        "stock": 100,
        "batches":{...}
    },
    "Warehouse32": {
        "stock": 90,
        "batches":{...}
    },
    "Warehouse58": {
        "stock": 43,
        "batches":{...}
    },
    "Warehouse99": {
        "stock": 55,
        "batches":{...}
    },
    "Warehouse101": {
        "stock": 12,
        "batches":{...}
    },
    "Warehouse151": {
        "stock": 99,
        "batches":{...}
    },
    "Warehouse152": {
        "stock": 44,
        "batches":{...}
    },
}

And what I’m looking on, it’s create a query that gets me all the stock of each warehouse like this:

"warehouse: {"Warehouse1": 100, "Warehouse32": 90, "Warehouse58": 43, ...}

Each row on the table has different keys, on some of them could be just one warehouse of multiple.

I’m still newbie on this kind of stuff with the jsonb columns, and what I have reasearched at the moment or get me close to the result is this: select warehouses->'Warehouse1'->'stock' from "general".inventory i ;

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT 
  sku,
  jsonb_object_agg(warehouse_name, (warehouse_data->>'stock')::int) AS warehouses,
  additional_info
FROM (
  SELECT 
    sku,
    warehouse_name,
    warehouse_data,
    additional_info
  FROM 
    products,
    jsonb_each(warehouses) AS warehouse(warehouse_name, warehouse_data)
) AS expanded
GROUP BY sku

    Reading the documentation, I found the answer with the function of jsonb_each, which gives me the key and the value from the column of warehouse.


  2. 0

    You can use the jsonb_each function. This function expands the JSONB object into a set of key-value pairs, which you can then aggregate.

    SELECT jsonb_object_agg(key, value->'stock') AS warehouse_stock
FROM (
    SELECT key, value
    FROM inventory, jsonb_each(warehouses)
) AS expanded;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search