Select Query with JSON column in Postgresql

 Recently I encountered a problem to use JSONB type column with Reportserver and log it in their forum. Since I have made the example, why not post it here? 

Here is an example to use JSONB type column in Postgresql 10 and newer.

Step 1: Create the sample database

CREATE TABLE public.products (
    id int4 NOT NULL PRIMARY KEY,
    "name" varchar NOT NULL,
    "product" jsonb NULL
);

Step 2: Add sample data

id|name       |product                                                               |
--|-----------|----------------------------------------------------------------------|
1|Orange bod |[{"id": 18, "name": "Orange Gala", "value": "1Bll-1-99-aaa"}]         |
2|Chicken Pie|[{"id": 4, "name": "Downtown Chicken Pie", "value": "1Bll-1-201-aaa"}]|
3|Apple Pie  |[{"id": 5, "name": "Apple Pie", "value": "1All-1-1000-xzp"}]          |

Step 3: Run an SQL query

SELECT name, jsonb_agg(t->'name') AS brand, jsonb_agg(t->'value') AS code 
FROM products, jsonb_array_elements(products.product) t GROUP BY name; 

The results

name       |brand                   |code               |
-----------|------------------------|-------------------|
Apple Pie  |["Apple Pie"]           |["1All-1-1000-xzp"]|
Orange bod |["Orange Gala"]         |["1Bll-1-99-aaa"]  |
Chicken Pie|["Downtown Chicken Pie"]|["1Bll-1-201-aaa"] |

Another method is to include a WHERE clause as shown below;

SELECT name, jsonb_agg(t->'name') AS brand, jsonb_agg(t->'value') AS code 
FROM products, jsonb_array_elements(products.product) t
WHERE t->>'name'='Orange Gala'
GROUP BY name

I hope this simple example will benefit you.