JSON and JSONB data formats in Netezza Performance Server

 View Only

JSON and JSONB data formats in Netezza Performance Server 

Tue August 25, 2020 05:41 PM

Authors: Rafael Jaffarove(raf.jaffarove@us.ibm.com), Hugh Nguyen(HughNguyen@ibm.com), Jia Li(jiali@us.ibm.com)

You can now store and retrieve JSON types natively. Without this feature you would have to use various character data types and store JSON objects as plain text. The new JSON feature will not only allow you to work directly with JSON data type, but also bridge the gap between relational data types and non-structured data. We now provide compatibility functions and storage of JSON data type in the textual form as well as its binary form known as JSONB. JSONB data type will be introduced later in this blog. In the future we plan to support another type, JSONPATH, to facilitate data manipulation within the JSON document even further.

Why do we support JSON data type natively in NPS?

JSON is a very commonly used data format, with such a wide range of applications, such as the input and output format for REST APIs and IoT device. In addition JSON is useful to bridge the gap between relational data types and non-structured data.

Let’s look at an example to demonstrate our new JSON data type in NPS.

Let’s say we have a sales catalog from a computer electronics stores. When the store first opened, we just had monitors to sell. It’s sufficient to use a relational table with traditional data types such as VARCHAR for the catalog. Our sales catalog first looks like this:

As the store sales grows, we have started to sell headphones. Now we need to add more columns to the existing catalog table because headphones come with some different attributes from monitors such as IS_WIRELESS, BATTERY_LIFE and COLOR. Now the new sales catalog looks like this:

As you can see, in order to accommodate new product attributes with traditional data types, we have to either add new columns to the existing table or add new table for new product. That’s when our new JSON data type comes in handy. We can use JSON data type column to make existing table extensible without any DDL change even if we have new product specs requirements coming in.

Let’s create the sales catalog now with a JSON data type column specs instead:

-- Using one single JSON column to store all product specs
CREATE TABLE sale_catalog_using_json(id     INT,
                                     name   VARCHAR(100),
                                     type   VARCHAR(100),
                                     price  FLOAT,
                                     specs  JSON);
INSERT INTO sale_catalog_using_json(id, name, type, price, specs)
    VALUES (1, 'Samsung - UR55',   'monitor',  349.99, '{"screen_size": 28, "refresh_rate": 60}');
INSERT INTO sale_catalog_using_json(id, name, type, price, specs)
    VALUES (2, 'LG 24ML44B-B',     'monitor',  179.99, '{"screen_size": 24, "refresh_rate": 75}');
INSERT INTO sale_catalog_using_json(id, name, type, price, specs)
    VALUES (3, 'Airpod Pro',       'earphone', 249.00, '{"is_wireless":true,"battery_life":4.5,"color":["white"]}');
INSERT INTO sale_catalog_using_json(id, name, type, price, specs)
    VALUES (4, 'Beats by Dr. Dre', 'earphone', 59.99,  '{"is_wireless": false, "color": ["red", "green", "black"]}');

As shown below, the 1st result set is from the sale_catalog  table without JSON column, and the 2nd result set is from sale_catalog_using_json table with JSON column. We can easily tell the benefits of JSON data type column.

  • We are able to store specifications for various products with different attributes within a single JSON type column.
  • We only insert data applicable to a specific type of product. We don’t have to waste space to store NULL values in non-applicable columns any more, such as SCREEN_SIZE for headphone in sale_catalog table with traditional data types. JSON data type helps to reduce unused space in this case.


How do we retrieve JSON data from NPS?

When we insert JSON data in text string, NPS will parse and validate the data against JSON format which makes JSON data retrieval fast and easy. Here are some sample queries against JSON data type to get you started.

  • Use jsonb_pretty to make JSON query result output more readable.
SELECT id, name, price, jsonb_pretty(specs) AS specs
from sale_catalog_using_json;

  • Use -> operator to query a specific JSON attribute, e.g. screen size in monitor specs
       specs -> 'screen_size' AS screen_size
FROM sale_catalog_using_json
WHERE type = 'monitor';

  • Use multiple -> operators to specify the index of a JSON element, e.g. the 1st element of all the color arrays for all the headphones 
       specs -> 'color' -> 0 AS color
FROM sale_catalog_using_json
WHERE type = 'earphone';

  • Use ->> operator to filter out certain element(s) from the JSON data, e.g. wireless headphone

FROM sale_catalog_using_json
WHERE specs ->> 'is_wireless' = 'true';

In addition, we offer a number of JSON operators and functions to facilitate data manipulation. For the complete list please refer to the Learn more section below.

What is the new JSONB data type in NPS?

The new JSONB data type will be used to store JSON unstructured data in a NPS database in binary form. From server perspective JSONB data type is simply the binary format of JSON type. They are exactly same from client perspective. User can define columns of type JSONB in regular tables just like JSON data type columns. Usage of JSONB data type is very similar to JSON data type. Data can be stored in JSONB columns via JSON or any string types which will be implicitly casted to JSONB.

Performance wise JSONB data type is much faster for server to process because it’s a lot faster to process binary than raw JSON string.

Use case

Here shows an IoT use case of JSON/JSONB data type and their life cycle in NPS databases.


Learn more

With today’s vast JSON based applications our new JSON/JSONB data types are essential and critical for our customers to work directly with JSON data in NPS databases. Our new JSON/JSONB functions and operators make data retrieval and manipulation a breeze. You can learn more about JSON/JSONB here. Give it a try and you will love them!




0 Favorited
0 Files