pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
nested xml/json to table
3+ messages / 2 participants
[nested] [flat]

* nested xml/json to table
@ 2023-03-17 10:05 Wim Bertels <wim.bertels@ucll.be>
  2023-03-17 10:21 ` Re: nested xml/json to table Thomas Kellerer <shammat@gmx.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Wim Bertels @ 2023-03-17 10:05 UTC (permalink / raw)
  To: pgsql-general

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 


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: nested xml/json to table
  2023-03-17 10:05 nested xml/json to table Wim Bertels <wim.bertels@ucll.be>
@ 2023-03-17 10:21 ` Thomas Kellerer <shammat@gmx.net>
  2023-03-17 11:04   ` Re: nested xml/json to table Wim Bertels <wim.bertels@ucll.be>
  0 siblings, 1 reply; 3+ messages in thread

From: Thomas Kellerer @ 2023-03-17 10:21 UTC (permalink / raw)
  To: Wim Bertels <wim.bertels@ucll.be>; pgsql-general

Wim Bertels schrieb am 17.03.2023 um 11:05:
> 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


You can't have a function that returns a different set of columns each time you call it
(without specifying the output columns - which you don't want).

I have once written a function to flatten a JSON hierarchy to multiple rows.

Applied to your first example it would return the following:

path                                             | key          | value
-------------------------------------------------+--------------+-----------------
/glossary                                        | title        | example glossary
/glossary/GlossDiv                               | title        | S
/glossary/GlossDiv/GlossList/GlossEntry          | ID           | SGML
/glossary/GlossDiv/GlossList/GlossEntry          | Abbrev       | ISO 8879:1986
/glossary/GlossDiv/GlossList/GlossEntry          | SortAs       | SGML
/glossary/GlossDiv/GlossList/GlossEntry          | Acronym      | SGML
/glossary/GlossDiv/GlossList/GlossEntry          | GlossSee     | markup
/glossary/GlossDiv/GlossList/GlossEntry          | GlossTerm    | Sta..
/glossary/GlossDiv/GlossList/GlossEntry/GlossDef | para         | A meta-m..
/glossary/GlossDiv/GlossList/GlossEntry/GlossDef | GlossSeeAlso | ["GML", "XML"]

And the following for the second example:

path   | key  | value
-------+------+------
/s1    | f1   | a
/s1    | f2   | b
/s1/f3 | f3.1 | c
/s1/f3 | f3.2 | d
/s1    | f1   | e
/s1    | f4   | g


Thomas

Attachments:

flatten_json.sql (text/plain, 1.3K) download create or replace function flatten(p_input jsonb, p_path text) returns table(path text, key text, value text) as $$ begin if jsonb_typeof(p_input) = 'array' then return query select f.* from jsonb_array_elements(p_input) as a(element) cross join flatten(a.element, p_path) f; else return query select p_path, e.key, e.value #>> '{}' from jsonb_each(p_input) as e(key, value) where jsonb_typeof(e.value) not in ('object', 'array') or (jsonb_typeof(e.value) = 'array' and jsonb_typeof(e.value -> 0) <> 'object') union all select f.* from jsonb_each(p_input) as t(key,value) cross join flatten(t.value, p_path||'/'||t.key) as f where jsonb_typeof(t.value) = 'object' union all select f.* from jsonb_each(p_input) as t(key,value) cross join jsonb_array_elements(t.value) as a(element) cross join flatten(a.element, p_path||'/'||t.key) as f where jsonb_typeof(t.value) = 'array' and jsonb_typeof(t.value -> 0) = 'object'; end if; end; $$ language plpgsql immutable parallel safe ; create or replace function flatten(p_input jsonb) returns table(path text, key text, value text) as $$ select * from flatten(p_input, ''); $$ language sql immutable parallel safe ;
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: nested xml/json to table
  2023-03-17 10:05 nested xml/json to table Wim Bertels <wim.bertels@ucll.be>
  2023-03-17 10:21 ` Re: nested xml/json to table Thomas Kellerer <shammat@gmx.net>
@ 2023-03-17 11:04   ` Wim Bertels <wim.bertels@ucll.be>
  0 siblings, 0 replies; 3+ messages in thread

From: Wim Bertels @ 2023-03-17 11:04 UTC (permalink / raw)
  To: shammat@gmx.net <shammat@gmx.net>; pgsql-general

Thomas Kellerer schreef op vr 17-03-2023 om 11:21 [+0100]:
> Wim Bertels schrieb am 17.03.2023 um 11:05:
> > 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)
> 
> 
> You can't have a function that returns a different set of columns
> each time you call it
> (without specifying the output columns - which you don't want).

Hello Thomas,

thanks for the feedback,
i was wondering in the likes of existing built-in functions or
extensions (not CREATE FUNCTION)

mvg,
Wim




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-03-17 11:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-17 10:05 nested xml/json to table Wim Bertels <wim.bertels@ucll.be>
2023-03-17 10:21 ` Thomas Kellerer <shammat@gmx.net>
2023-03-17 11:04   ` Wim Bertels <wim.bertels@ucll.be>

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