SQL Query with JSON column in Postgresql

I still use JSON datatypes in databases, and its proving to its worth. Postgresql loads the whole row during queries and this reduce access to other tables for join and retains data, instead of relying on dynamic changes of another table. JSONB type column is supported by Reportserver.net which I have mentioned in previous post. 

Here is an example to use JSONB type column in Postgresql 12 and newer. Difference from previous post, here its saved as an object instead of an array. Use of JSON or JSONB type is very much determined by the data intended for storage and retrieval.

Points to note:

Symbol ->

meaning: Get JSON array element (indexed from zero, negative integers count from the end)

Or Get JSON object field by key

Symbol: ->>

meaning: Get JSON array element as text Or Get JSON object field as text

Symbol: @>

meaning: Does the left JSON value contain the right JSON path/value entries at the top level?

Step 1: Create the sample database

CREATE TABLE public.products (

id serial NOT NULL,

"name" varchar(255) NOT NULL,

unit varchar NOT NULL, -- limit to 2 decimals

product jsonb NOT NULL,

created_at timestamp(0) NULL,

updated_at timestamp(0) NULL,

CONSTRAINT products_pkey PRIMARY KEY (id)

);

Step 2: Add sample data

Step 3: Run an SQL query

Display list of data as is.

SELECT id, name, product from products;

id|name       |product                                                                                                |

--|-----------|-------------------------------------------------------------------------------------------------------|

 3|Apple Pie  |{"id": 5, "name": "Apple Pie", "details": {"value": "1All-1-1000-xzp", "source": "Malaysia"}}          |

 1|Orange Bod |{"id": 18, "name": "Orange Gala", "details": {"value": "1Bll-1-99-aaa", "source": "Malaysia"}}         |

 2|Chicken Pie|{"id": 4, "name": "Downtown Chicken Pie", "details": {"value": "1Bll-1-201-aaa", "source": "Malaysia"}}|

Display columns id, name and product->details

SELECT id, name, product->'details' from products;

or

SELECT id, name, product->'details' as details from products;

Display columns id, name and sub contents of a json data

select id, name, (product->'details')::jsonb->'source' as details from products;

The WHERE clause

Lets retrieve rows where jsonb element key 'name' have a specific value.

SELECT id, name, product->'details' as details from products

where product->>'name'= 'Apple Pie'

Retrieve rows where jsonb element key 'name' have a wildcard (%) value.

select id, name, product->'details' as details from products

where product->>'name' like 'Apple %'

Retrieve rows where jsonb sub element key 'value' have a wildcard (%) value.

select id, name, product->'details' as details from products

where product->'details'->>'value'::text like  '1All-1-%'

Retrieve rows where jsonb element have a specific key:value

select id, name,  product as details from products

where product @> '{"name":"Orange Gala"}'

I hope these examples will benefit you.