pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Wim Bertels <wim.bertels@ucll.be>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: nested xml/json to table
Date: Fri, 17 Mar 2023 10:05:18 +0000
Message-ID: <05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be> (raw)

Hello,

in python pandas there is for example a json_normalize function,
i didn't find something similar or better in postgresql?

what would be the general idea: "easily" convert an hierarchical
structure like json or xml to a table; for example creating columns by
appending the key-names when going doing down the three, using null for
empty values, adding more columns as needed by the given structure.
(1-way operation)

a few conceptual gists:
jsonX=
{
    "glossary": {
        "title": "example glossary",
		"GlossDiv": {
            "title": "S",
			"GlossList": {
                "GlossEntry": {
                    			"ID": "SGML",
					"SortAs": "SGML",
					"GlossTerm": "Sta.."; 
					"Acronym": "SGML",
					"Abbrev": "ISO 8879:1986",
					"GlossDef": {
			                        "para": "A meta-m..",
						"GlossSeeAlso": 
							["GML", "XML"]
                    			},
					"GlossSee": "markup"
                }
            }
        }
    }
}

select *
from json_to_table('jsonX');
-- generated columns with no data/only nulls could be removed..
-- arrays could be unnested in the process as well

glossary | glossary.title   | glossary.title.GlossDiv.title | .. 
-------------------------------------------------------------
null	 | example glossary | S			            |
..

the last column: glossary.title.GlossDiv.GlossList.GlossEntry.GlossSee
with value "markup"

---

what if there are different structures that need to be combined?
(they could be added in the same manner as before)

jsonY=
{
s1:[{
    "f1": "a",
    "f2": "b",
    "f3": { "f3.1": "c",
	    "f3.2": "d"}
   },
   { 
    "f1": "e",
    "f4": "g"
   }
   ]
}
           
select *
from json_to_table('jsonY');
-- generated columns with no data/only nulls could be removed..
-- separator sign is untrusted

s1  | s1.f1 | s1.f2 | s1.f3 | s1.f3.f3.1 | s1.f3.f3.2 | s1.f4
-------------------------------------------------------------
null| a     | b     | null  | c          | d          | null
null| e     | null  | null  | null       | null       | g


any ideas or suggestions (apart from plpython)? 
Wim 


view thread (3+ messages)  latest in thread

Message-ID: <05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be>
Permalink:  ../05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be/
Also on:    postgresql.org/message-id/05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: wim.bertels@ucll.be
  Subject: Re: nested xml/json to table
  In-Reply-To: <05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be>
  References: <05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox