Use PASSING with JSON_TABLE() To Make Calculations

I ran across this trick while preparing a talk for the Southern California Linux Expo  (SCaLE) on using JSON_TABLE(). This is a 'reserve' talk, which means it will presented IF another speaker drops out. But I thought this was interesting enough to share here. I will make sure the slides are posted online when finished.

Passing Interest

JSON_TABLE() temporarily converts JSON data into a relational table so that the temporary table can be processed by Structured Query Langauge (SQL) Commands. A feature in the PostgreSQL implementation is the passing clause.  In the example below, you will see PASSING 1500 AS price_limit in red. This declares price_limit as a variable and assigns a value of 1500 to it. 

Later, in the body of the JSON_TABLE() call, you will see in green is_too_expensive BOOLEAN EXISTS PATH '$.price ? (@ > $price_limit)'.  Here a variable is created named is_too_expensive of type BOOLEAN, which means we get a true or false answer from the rest of the declaration. If the price column has a value, it is compared with the price_limit value to see if that value is greater or not than the limit. 

demo=# SELECT *
FROM JSON_TABLE(
    '{"guitars":[{"item": "Fender Telecaster", "price": 1000}, 
                 {"item": "Gibson Les Paul", "price": 2000},
                 {"item": "PRS semi-hollowbody", "price": 1500}]}',
    '$.guitars[*]' PASSING 1500 AS price_limit
    COLUMNS (
     row_num for ordinality,
        Guitar TEXT PATH '$.item',
        Price INT PATH '$.price',
        is_too_expensive BOOLEAN EXISTS PATH '$.price ? (@ > $price_limit)'
    )
) AS my_example;
 row_num |       guitar        | price | is_too_expensive 
---------+---------------------+-------+------------------
       1 | Fender Telecaster   |  1000 | f
       2 | Gibson Les Paul     |  2000 | t
       3 | PRS semi-hollowbody |  1500 | f
(3 rows)
demo=# 

How Does This Help You?

JSON data is tough on relational databases as it can not be processed with the regular operators found in SQL without special calls. Those calls can slow down a query tremendously. We could have pulled the data from JSON and then used another query to make our quantitative determination. But that is another query on top of the JSON_TABLE() call, and two queries are going to be slower than one. Or we could have used some sort of case statement. But this is such a simple calculation that it seems tragic to separate it from the rest of the data.

This is a quick and 'not dirty' to handle the data.

And if you are at SCaLE, please day 'hello!'


Comments

Post a Comment

Popular posts from this blog

How Does PostgreSQL Store Your Data?

PostgreSQL is an amazing database. And some of us wonder how it takes what we enter on a keyboard and stores it. The following is peeking into the 'clockworks' to see how things are done. Step 1 On a fresh install of Ubuntu with PostgreSQL 17, let us create a database. stoker@ThinkPad:~$ createdb demo Password:  stoker@ThinkPad:~$ psql demo Password for user stoker:  psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1)) Type "help" for help. Now that we are in this new database, we can ask where PostgreSQL stores the data by looking for the data directory. demo=# show data_directory;        data_directory         -----------------------------  /var/lib/postgresql/17/main (1 row) This informs us that our data will be somewhere under the /var/lib/postgresql17/main directory.  Step 2 Issuing a command to create a table starts of series of events that we will study in later posts. Right now, we want to see where the stuff goes. Here a simp...
Read more

Now That We Know Where PostgreSQL Stores Data, We Can Look At How

 Last time , we started looking at how PostgreSQL stores data by discovering where the data is stored and had a quick peek at that data. There is only one record at this point. demo=# SELECT * FROM ex01;  a | b  ---+---  1 | 1 (1 row) And the contents of that file are not precisely illuminating at first glance. For now, just trust me that our row is in there, and you will see where in just a little bit. root@ThinkPad:/var/lib/postgresql/17/main/base/16389# od -c 16390 0000000  \0  \0  \0  \0   (   z 227 001  \0  \0  \0  \0 034  \0 340 037 0000020  \0     004      \0  \0  \0  \0 340 237   @  \0  \0  \0  \0  \0 0000040  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 * 0017740 362 002  \0  \0  \0  \0  \...
Read more

How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...
Read more