Google+ Followers

maandag 8 september 2014

Echte wereld PostgreSQL: van XML naar SQL+JSON

Stel je hebt een databron die XML aanlevert en die gegevens moeten in een database terecht komen.

Peulenschil.


Stel je hebt dit XML bestandje:

<articles>
<article>
  <title>Dit is de titel</title>
  <body>Wow, dit is kazig!</body>
  <fotos>
    <foto width="100" height="200">kaas.png</foto>
    <foto width="100" height="200">cheese.png</foto>
  </fotos>
  </article>
<article>
  <title>Appels en peren</title>
  <body>Onvergelijkbare vruchten.</body>
  <fotos>
    <foto width="100" height="200">appel.png</foto>
    <foto width="100" height="200">peer.png</foto>
    <foto width="100" height="200">peren.png</foto>
    <foto width="100" height="200">appelmoes.png</foto>
  </fotos>
  </article>
</articles>

En dat moet in een tabel:

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (id SERIAL, title TEXT, body TEXT, fotos JSON);


Stap 1: Maak een CTE die het XML document opdeelt in brokjes per artikel.

De "sourcedata" CTE is hier alleen gebruikt om de XML te kunnen gebruiken zonder hem eerst in een tabel op te hoeven slaan.

WITH sourcedata AS (SELECT '<articles>
<article>
  <title>Dit is de titel</title>
  <body>Wow, dit is kazig!</body>
  <fotos>
    <foto width="100" height="200">kaas.png</foto>
    <foto width="100" height="200">cheese.png</foto>
  </fotos>
  </article>
<article>
  <title>Appels en peren</title>
  <body>Onvergelijkbare vruchten.</body>
  <fotos>
    <foto width="100" height="200">appel.png</foto>
    <foto width="100" height="200">peer.png</foto>
    <foto width="100" height="200">peren.png</foto>
    <foto width="100" height="200">appelmoes.png</foto>
  </fotos>
  </article>
</articles>'::xml AS x)
, articles AS (SELECT UNNEST(XPATH('/articles/article', x)) AS article FROM sourcedata)
SELECT * FROM articles;





Met XPATH() worden de XML fragmenten uit de XML gehaald. XPATH() geeft een array van gevonden elementen terug en die worden met UNNEST() omgezet naar rijen.


Step 2: Verwerk de XML fragmenten


WITH sourcedata AS (SELECT '<articles>
<article>
  <title>Dit is de titel</title>
  <body>Wow, dit is kazig!</body>
  <fotos>
    <foto width="100" height="200">kaas.png</foto>
    <foto width="100" height="200">cheese.png</foto>
  </fotos>
  </article>
<article>
  <title>Appels en peren</title>
  <body>Onvergelijkbare vruchten.</body>
  <fotos>
    <foto width="100" height="200">appel.png</foto>
    <foto width="100" height="200">peer.png</foto>
    <foto width="100" height="200">peren.png</foto>
    <foto width="100" height="200">appelmoes.png</foto>
  </fotos>
  </article>
</articles>'::xml AS x)
, articles AS (SELECT UNNEST(XPATH('/articles/article', x)) AS article FROM sourcedata)
, fotos    AS (SELECT article, UNNEST(xpath('fotos/foto/text()', article))::text AS foto FROM articles)
, fotojson AS (SELECT article::TEXT, JSON_AGG(foto) AS fotos_json  FROM fotos GROUP BY article::TEXT)
SELECT (xpath('title/text()', article::xml))[1]::text, (xpath('body/text()', article::xml))[1]::text, fotos_json::JSON  FROM fotojson;

Nadat de artikelen zijn opgehaald en ge-unnest, worden de fotos opgehaald, ge-unnest en met JSON_AGG() platgeslagen tot een json-array per artikel.
Vervolgens wordt uit het artikel de title, body en de foto-array opgehaald.


Stap 3: Prop de data in de doeltabel:

WITH sourcedata AS (SELECT '<articles>
<article>
  <title>Dit is de titel</title>
  <body>Wow, dit is kazig!</body>
  <fotos>
    <foto width="100" height="200">kaas.png</foto>
    <foto width="100" height="200">cheese.png</foto>
  </fotos>
  </article>
<article>
  <title>Appels en peren</title>
  <body>Onvergelijkbare vruchten.</body>
  <fotos>
    <foto width="100" height="200">appel.png</foto>
    <foto width="100" height="200">peer.png</foto>
    <foto width="100" height="200">peren.png</foto>
    <foto width="100" height="200">appelmoes.png</foto>
  </fotos>
  </article>
</articles>'::xml AS x)
, articles AS (SELECT UNNEST(XPATH('/articles/article', x)) AS article FROM sourcedata)
, fotos    AS (SELECT article, UNNEST(xpath('fotos/foto/text()', article))::text AS foto FROM articles)
, fotojson AS (SELECT article::TEXT, JSON_AGG(foto) AS fotos_json  FROM fotos GROUP BY article::TEXT)
INSERT INTO articles (title, body, fotos) 
SELECT (xpath('title/text()', article::xml))[1]::text, (xpath('body/text()', article::xml))[1]::text, fotos_json::JSON  FROM fotojson;




En nu kun je allerlei ongein uithalen, zoals opvragen hoeveel foto's er zijn, en wat de eerste foto is:

SELECT id, title, body, json_array_length(fotos) AS num_fotos, fotos->>0 AS eerste_foto FROM articles;