PostgreSQL is an awesome database, with an awesome data type, JSON. It actually has two JSON data types, json and jsonb.

JSON data types are for storing JSON (JavaScript Object Notation) data.

There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

• It is typically recommended to use jsonb.
• PostgreSQL is rich in functions and operators for querying this data type.

Our Goal

In this article we will focus on one function in particular:

jsonb_to_recordset(jsonb data).

This function is particularly useful when you have an Array of Objects and want to query against the Values of one or more Keys using standard comparison operators including wildcards.

For example, we may have jsonb data like this, showing products purchased by a customer.

[{
  "productid": "3",
  "name": "Virtual Keyboard",
  "price": "150.00"
}, {
  "productid": "1",
  "name": "Dell 123 Laptop Computer",
  "price": "1300.00"
},
 {
  "productid": "8",
  "name": "LG Ultrawide Monitor",
  "price": "190.00"
}
]

If we have several customers and their purchases stored in a jsonb column, we may want to know things like,

• Who bought Virtual Keyboards (Where name = ‘Virtual Keyboard’)
• Who bought any kind of Keyboard (Where name Like ‘%Keyboard)
• Who bought a Laptop and Keyboard (Where name Like ‘%Laptop%’ or name Like ‘%Keyboard’)
• How many of each product (name) has been purchased.

and many others, of course.

---

Assumptions

I will assume you have PostgreSQL and something along the lines of pgAdmin.

---

Let’s Get Started

Building Our Data

1. Open **pgAdmin **and create a database as desired.
2. Right-Click on the database name and choose Query Tool.
3. Run the snippet below to create a simple table that will have an id, purchaser name and a**_ jsonb column that stores an array of json objects, which will store items purchased._**

CREATE TABLE public.purchases
(
    id   serial PRIMARY KEY,
    purchaser varchar(50),
    items_purchased jsonb
);

4. Run the snippet below to insert four records in to the table.

INSERT INTO purchases (purchaser,items_purchased) VALUES ('Bob','[{
  "productid": "1",
  "name": "Dell 123 Laptop Computer",
  "price": "1300.00"
},
 {
  "productid": "2",
  "name": "Mechanical Keyboard",
  "price": "120.00"
}
]');
INSERT INTO purchases (purchaser,items_purchased) VALUES ('Carol','[{
  "productid": "3",
  "name": "Virtual Keyboard",
  "price": "150.00"
}, {
  "productid": "1",
  "name": "Dell 123 Laptop Computer",
  "price": "1300.00"
},
 {
  "productid": "8",
  "name": "LG Ultrawide Monitor",
  "price": "190.00"
}
]');
INSERT INTO purchases (purchaser,items_purchased) VALUES ('Ted','[{
  "productid": "6",
  "name": "Ergonomic Keyboard",
  "price": "90.00"
},
 {
  "productid": "7",
  "name": "Dell 789 Desktop Computer",
  "price": "120.00"
}
]');
INSERT INTO purchases (purchaser,items_purchased) VALUES ('Alice','[{
  "productid": "7",
  "name": "Dell 789 Desktop Computer",
  "price": "120.00"
},
 {
  "productid": "2",
  "name": "Mechanical Keyboard",
  "price": "120.00"
}
]');

#api-development #postgresql #nosql #postgres #json