Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pd7DI-00046t-Tp for pgsql-general@arkaria.postgresql.org; Fri, 17 Mar 2023 10:21:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pd7DH-0002EF-1U for pgsql-general@arkaria.postgresql.org; Fri, 17 Mar 2023 10:21:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pd7DG-0002E6-Hz for pgsql-general@lists.postgresql.org; Fri, 17 Mar 2023 10:21:30 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pd7DD-0004aM-N8 for pgsql-general@postgresql.org; Fri, 17 Mar 2023 10:21:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1679048484; i=shammat@gmx.net; bh=3pFwvenK073x/fIwFW2G+ZuNIV4EXd12ebjoRze24no=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=BFC07VZacC1hGZbFHohJAfmY6OdJu5h+nmbC+7+ufpcCczWoeq8IszbKcUG2aCkaf B84JIMUQNtLi0lqkD/OGfTDUXuRJwVLu+SKumiTTRrJjSM9tCPH+flPZP0HKt4CZaC r4/xz3MUPy37ujKXMM2a2T2plX4QcCIR+lAJBUUX9e+crKLC8jVb8/4DGWUKopxwaY QPubcKP2Dp/Gpku4iFceo+bOaq1Bhr7Kdfir4ts3pX0/qIOmJsAOKI8D5mu6I5tbFU hkhhZ+MYri5A0IHjZ3tNLoNAcE1bYs6iD2ZzSlltpGGOF/yhvyHJulvXqR0/qb4LGV /442ZOQzxQs8Q== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.8.0.19] ([185.40.248.10]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MatRT-1qFSpz13xz-00cRHz; Fri, 17 Mar 2023 11:21:24 +0100 Subject: Re: nested xml/json to table To: Wim Bertels , "pgsql-general@postgresql.org" References: <05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be> From: Thomas Kellerer Message-ID: <90741398-62ca-3bd5-79d0-c0f6a2eef4cf@gmx.net> Date: Fri, 17 Mar 2023 11:21:23 +0100 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666 MIME-Version: 1.0 In-Reply-To: <05b3f3beccba4ce16d2a4ad411085ae5c46e2e82.camel@ucll.be> Content-Type: multipart/mixed; boundary="------------8647DD62BA5CEC5323ACB042" Content-Language: de-DE X-Provags-ID: V03:K1:N/wI11SAITTueS0HnRkATIO473Dp1nEL6WAKPNECQ68CznDPdA1 VDGzSBWgFLZ1Btrt1WqyExDd0hyUbIpchaBHxl4xyRWVyGQwc6Q1z1TWEYEUa9g8FGM5ALJ 9RL+lb/cj2FazP2T6knhHt3f47C6UVn/QrB1LI+lnwnSLrpLlRsSZFQHY9QipqYEuhSuHpl +dsZxLCxWtCH+gpY9EWDw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:v0Y8tPHCANs=;4qmZp1kmIoVAcvaxsXqPfHBdlUL 76r0MGJYiA37b3HjQwwD9rdo1XmWVm7+jmlGaXeu5i2L/5GF4f6dlQE+g7CEKDLwFZ3J0XYn3 wSJWCieflkzxgDbQ4gRL9oyA453RGa3CqGNTN6kdvM5yAyTe0kjVtB0L/2wsCBcgRiZrxItJW pMAaWp9P013nx3CH8fkQZxGf0P1RCYaLpkA5bQVDhMuQHYOahc0Al1bJASUtAJ6LVvriy3CNJ GvKNlePgkpoOblsGTQSECEQgV37knp0LsJc5fVlxNAp7t3847l55xdl70flQ31znSPQYfS16F oF47i3TfNj87v900xArSN9c+6VnSkkosBKggggwrbpmzF7MZfLBPbGb2HHc6FdKyJOoeBIjgn DHdpJH8X5BhySg6fZijgbIc/ZGD6K+rj3/8C1N9mh8L9e0NhpCJFlYMnJ8ZPyN+y4Bs6tpqq2 RLkZcIzECEbkvjHEpTPNhT0oesBqqKcvwbadQuLxgBcuTZv2nyOa9Ew/J7OQCuinBMaqBqpVu 0BXR83m4N5YgKluBKAwV+VFqpelbTEzaWR7V3tnV3Cnd54H1lYhaAnBsjiyyDV/ReMC1jsfcg VM3iq9Y+GA1/qtrWM4Ldxf9z/Xa4s4OoD/nHI/Wynjiqx6dCdEcSK2tybwvMA45LXSC5IvmSn jRwNyu4fS++79O9mHFPE/pTxij+iqUU3jst6tnkOdOi9YfJpFezU6gbYc9KsbSHobEJDp+RAb P4QN7wBwFGJoyg+761eHaH1N+7iHIC2QeVzOx/RLZJh8XIxXEg6mPdfr4k09rPv7AyTZBscgN DU7b/x7SV8z8TxK/+Q+8qwfNQCtAUgjAF0DIS4pPjk7qftwuv2UWF1mvQ7Q+wGRD8iAwpfAEV cOtGl7SRw6rawRlXajwrdt80IDMVQMUsZqgpXTyLVyVmbDLhj0Y9LwNNZ535A2NqlIjqVHMwV 8VJxTA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------8647DD62BA5CEC5323ACB042 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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=3D > { > "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":=C2=A0 > ["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=3D > { > 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 tim= e 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 row= s. Applied to your first example it would return the following: path | key | value =2D------------------------------------------------+--------------+-------= ---------- /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 =2D------+------+------ /s1 | f1 | a /s1 | f2 | b /s1/f3 | f3.1 | c /s1/f3 | f3.2 | d /s1 | f1 | e /s1 | f4 | g Thomas --------------8647DD62BA5CEC5323ACB042 Content-Type: text/plain; charset=UTF-8; name="flatten_json.sql" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="flatten_json.sql" Y3JlYXRlIG9yIHJlcGxhY2UgZnVuY3Rpb24gZmxhdHRlbihwX2lucHV0IGpzb25iLCBwX3Bh dGggdGV4dCkNCiAgcmV0dXJucyB0YWJsZShwYXRoIHRleHQsIGtleSB0ZXh0LCB2YWx1ZSB0 ZXh0KQ0KYXMNCiQkDQpiZWdpbg0KICBpZiBqc29uYl90eXBlb2YocF9pbnB1dCkgPSAnYXJy YXknIHRoZW4NCiAgICByZXR1cm4gcXVlcnkNCiAgICAgIHNlbGVjdCBmLioNCiAgICAgIGZy b20ganNvbmJfYXJyYXlfZWxlbWVudHMocF9pbnB1dCkgYXMgYShlbGVtZW50KQ0KICAgICAg ICBjcm9zcyBqb2luIGZsYXR0ZW4oYS5lbGVtZW50LCBwX3BhdGgpIGY7DQogIGVsc2UNCiAg ICByZXR1cm4gcXVlcnkNCiAgICAgIHNlbGVjdCBwX3BhdGgsIGUua2V5LCBlLnZhbHVlICM+ PiAne30nDQogICAgICBmcm9tIGpzb25iX2VhY2gocF9pbnB1dCkgYXMgZShrZXksIHZhbHVl KQ0KICAgICAgd2hlcmUganNvbmJfdHlwZW9mKGUudmFsdWUpIG5vdCBpbiAoJ29iamVjdCcs ICdhcnJheScpDQogICAgICAgIG9yIChqc29uYl90eXBlb2YoZS52YWx1ZSkgPSAnYXJyYXkn IGFuZCBqc29uYl90eXBlb2YoZS52YWx1ZSAtPiAwKSA8PiAnb2JqZWN0JykNCg0KICAgICAg dW5pb24gYWxsDQoNCiAgICAgIHNlbGVjdCBmLioNCiAgICAgIGZyb20ganNvbmJfZWFjaChw X2lucHV0KSBhcyB0KGtleSx2YWx1ZSkNCiAgICAgICAgY3Jvc3Mgam9pbiBmbGF0dGVuKHQu dmFsdWUsIHBfcGF0aHx8Jy8nfHx0LmtleSkgYXMgZg0KICAgICAgd2hlcmUganNvbmJfdHlw ZW9mKHQudmFsdWUpID0gJ29iamVjdCcNCg0KICAgICAgdW5pb24gYWxsDQoNCiAgICAgIHNl bGVjdCBmLioNCiAgICAgIGZyb20ganNvbmJfZWFjaChwX2lucHV0KSBhcyB0KGtleSx2YWx1 ZSkNCiAgICAgICAgY3Jvc3Mgam9pbiBqc29uYl9hcnJheV9lbGVtZW50cyh0LnZhbHVlKSBh cyBhKGVsZW1lbnQpDQogICAgICAgIGNyb3NzIGpvaW4gZmxhdHRlbihhLmVsZW1lbnQsIHBf cGF0aHx8Jy8nfHx0LmtleSkgYXMgZg0KICAgICAgd2hlcmUganNvbmJfdHlwZW9mKHQudmFs dWUpID0gJ2FycmF5Jw0KICAgICAgICBhbmQganNvbmJfdHlwZW9mKHQudmFsdWUgLT4gMCkg PSAnb2JqZWN0JzsNCiAgZW5kIGlmOw0KZW5kOw0KJCQNCmxhbmd1YWdlIHBscGdzcWwNCmlt bXV0YWJsZQ0KcGFyYWxsZWwgc2FmZQ0KOw0KDQpjcmVhdGUgb3IgcmVwbGFjZSBmdW5jdGlv biBmbGF0dGVuKHBfaW5wdXQganNvbmIpDQogIHJldHVybnMgdGFibGUocGF0aCB0ZXh0LCBr ZXkgdGV4dCwgdmFsdWUgdGV4dCkNCmFzDQokJA0KICBzZWxlY3QgKg0KICBmcm9tIGZsYXR0 ZW4ocF9pbnB1dCwgJycpOw0KJCQNCmxhbmd1YWdlIHNxbA0KaW1tdXRhYmxlDQpwYXJhbGxl bCBzYWZlDQo7 --------------8647DD62BA5CEC5323ACB042--