2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Andrew's PlanetPostgreSQL3 / JSON version of XMLTABLE example
Andrew Dunstan

JSON version of XMLTABLE example

March 14, 2017/5 Comments/in Andrew's PlanetPostgreSQL /by Andrew Dunstan

My colleague Álvaro Herrera gave a terrific explanation of the new XMLTABLE feature from Pavel Stěhule that he’s put a huge amount of effort into, and finally recently committed. I thought it would be fun to see how the example he gave might work today with JSON.

First, I came up with a JSON equivalent version of his piece of XML. Notice that in JSON, unlike in XML, containers are nameless, so we have no “room”  tags, for example, a room is just an object in the relevant array. This is what I came up with:

CREATE TABLE IF NOT EXISTS hoteldata AS SELECT json
$[
   {"id": "mancha", "name": "La Mancha",
    "rooms": [
      {"id": "201", "capacity": 3, "comment": "Great view of the Channel"},
      { "id": "202", "capacity": 5 }
    ],
   "personnel": [
      {"id": "1025","name": "Ferdinando Quijana",
       "salary": { "currency": "PTA", "amount": 45000}
      }
   ]
 },
 {"id": "valpo", "name": "Valparaíso",
  "rooms": [
    {"id": "201", "capacity": 2, "comment": "Very Noisy"},
    {"id": "202", "capacity": 2}
  ],
  "personnel": [
    {"id": "1026", "name": "Katharina Wuntz",
    "salary": { "currency": "EUR", "amount": 50000}
    },
    {"id": "1027", "name": "Diego Velázquez",
    "salary": { "currency": "CLP", "amount": 1200000}
    }
  ]
 }
]$ AS hotels;

Now the problem in creating a query with this data that mimics Álvaro’s first query is that the individual room data is nested inside an array, while the hotel name is in the parent object of that array. XPATH is very good at combining data at different nesting levels, but we don’t have that here so we need to be a bit more creative. The strategy is to break the hotels data into individual rows, one for each hotel, and then with each hotel combine its name with each room. Note that combining values is currently a jsonb-only operation. I decided for now not to bother about the row numbering piece – that should be easily added if necessary with a call to the row_number() window function.

Here’s the first query I came up with to implement that strategy:

with the_hotels as
(
 select hotel from hoteldata, json_array_elements(hotels) h(hotel) 
),
flattened as
(
 select jsonb_build_object('hotel', hotel->>'name') || room::jsonb as rm
 from the_hotels, json_array_elements(the_hotels.hotel->'rooms') r(room)
)
select j.hotel as hotel_name, j.id as room_id, j.capacity, j.comment
from flattened, 
 jsonb_to_record(rm) as 
 j ("id" int, "capacity" int, "hotel" text, "comment" text);

And the result looks like this:

 hotel_name | room_id | capacity |          comment          
------------+---------+----------+---------------------------
 La Mancha  |     201 |        3 | Great view of the Channel
 La Mancha  |     202 |        5 | 
 Valparaíso |     201 |        2 | Very Noisy
 Valparaíso |     202 |        2 | 
(4 rows)

Not bad! Pretty close, but we’re missing the default values for the comments. Well, the json functions have support for that, too. Instead of using jsonb_to_record, we need to use a named type with jsonb_populate_record. Then we can supply a value for the record to populate that has the default value. Note that the named type needs to have the same column names that the jsonb data has.

Here’s how that looks:

create type hotelroom as 
("id" int, "capacity" int, "hotel" text, "comment" text);

with the_hotels as
(
    select hotel from hoteldata, json_array_elements(hotels) h(hotel) 
),
flattened as
(
    select jsonb_build_object('hotel', hotel->>'name') || room::jsonb as rm
    from the_hotels, json_array_elements(the_hotels.hotel->'rooms') r(room)
)
select j.hotel as hotel_name, j.id as room_id, j.capacity, j.comment
from flattened, 
    jsonb_populate_record(row(null,null,null,'A regular room')::hotelroom,
                          rm) as j;

And the result:

 hotel_name | room_id | capacity |          comment          
------------+---------+----------+---------------------------
 La Mancha  |     201 |        3 | Great view of the Channel
 La Mancha  |     202 |        5 | A regular room
 Valparaíso |     201 |        2 | Very Noisy
 Valparaíso |     202 |        2 | A regular room
(4 rows)

And there we are.

There is more json functionality coming to Postgres, but you can do quite a lot right now.

By the way, just to emphasize, XMLTABLE is a cool feature.

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
5 replies
  1. Colin 't Hart
    Colin 't Hart says:
    March 14, 2017 at 9:44 pm

    I’m wondering if it’s logically and technically feasible to have a JOSNTABLE (or JSONBTABLE) ?

    Reply
    • Andrew Dunstan
      Andrew Dunstan says:
      March 14, 2017 at 11:37 pm

      Yes, plans seem to be in the wind.

      Reply
      • Nikita Glukhov
        Nikita Glukhov says:
        March 20, 2017 at 2:19 pm

        Using JSON_TABLE from our PostgresPro’s SQL/JSON standard implementation this query will look like

        SELECT
        jsontable.*
        FROM
        hoteldata,
        JSON_TABLE (
        hotels, ‘$[*]’
        COLUMNS (
        hotel_name text PATH ‘$.name’,
        NESTED PATH ‘$.rooms[*]’ COLUMNS (
        room_id text PATH ‘$.id’,
        capacity int,
        comment text DEFAULT ‘A regular room’ ON ERROR
        )
        )
        ) jsontable;

        See:
        https://github.com/postgrespro/sqljson
        https://commitfest.postgresql.org/14/1063/

        Reply
  2. MarHoff
    MarHoff says:
    March 15, 2017 at 1:19 pm

    And wouldn’t it be nice to have a xml2jsonb conversion function so we could have the best of both worlds?

    Current postgresql jsonb toolkit is already impressive and keep growing. It would be nice to have a way to convert XML data object to jsonb for thoses who chose to work primarily with jsonb going forward.

    I found several xml2json projects in various languages but I really think this would be another plus for postgresql to have this built-in, don’t you think so?

    Reply
    • Andrew Dunstan
      Andrew Dunstan says:
      March 15, 2017 at 3:11 pm

      Well, that’s a bit off-topic. This should be fairly doable as an extension. Or you could write a plperl or plpython or possibly plv8 function to do it fairly easily too.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
XMLTABLE in PostgreSQL Support for enums in btree_gin and btree_gist
Scroll to top
×