json i postgresql tabell

Har några stora 100GB json filer som admin läst in från en S3 bucket till en postgresql tabell.

Det hamnar som en lång sträng i en kolumn i db tabellen och i kolumnen söker man med postgresql

När man bygger sql:n ska man studera json schemat och se på hur 'djupt' i strukturen den entitet man behöver är, ska man följa en path, "array", array", "list", "array", "list", "list", etc

array är omsluten av fyrkantparenteser [

list av krulliga parenteser {

Nedan en sträng kopierad från db tabellen, klistrad i notepad++ och formatterat till json:
https://ibb.co/D1HfVHZ

Det fungerar superfint men är enormt tidsödande den resulterande sql kan se ut som t ex:

(Ovanstående behöver inte stämma med bilden det är bara exempel)
Normalt sett blir sql statements mycket stora, ovanstående bara ett litet smakprov.

DET JAG SÖKER ÄR DETTA OCH INGENTING ANNAT.

Jag efterlyser en tool som jag matar med json schema eller json filen (kopierad från db tabellen, jag har inte tillgång till original json filen) och den entiteten jag söker och den returnerar den färdiga sql statement.

Metodiken kan jag, det är den jag tillämpar för att bygga upp sql statement, den skulle jag kunna implementera i py eller vba men jag inbillar mig att det måste finnas färdiga tools för ändamålet.

har googlat såklart.

How to Query a JSON Column in PostgreSQL
https://popsql.com/learn-sql/postgre...-in-postgresql

PostgreSQL JSON
https://www.postgresqltutorial.com/postgresql-json/

Känner inte till ngt sånt verktyg. Men funderade på arkitekturen här. Vore det inte bättre att översätta JSON till vanliga tabeller? Då får du betydligt enklare SQL och du bör få bättre prestanda och en mindre krånglig/awkward lösning?

Du kan inte använda json(b)_path_query? Finns sen version 12...

https://www.postgresql.org/docs/14/functions-json.html
https://www.postgresql.org/docs/14/d...PATH-ACCESSORS

Övriga utvecklingsfrågor --> Databaser
/Moderator

Förstår inte varför du ska använda SQL för att tolka en JSON-fil. Då är det väl bättre att importera den till SQL-databasen först och hämta data från tabellen, även om filen är extremt stor - så det kanske är bättre att "tvätta" den först.

Om du enbart behöver få ut data i ett visst återkommande fält så kan du lösa detta med RegEx.
Om det är mer komplicerat än så skulle jag i första hand gå tillbaka till datakällan och göra ett urval där istället så du inte får med dig så mycket "fluff".
Om det inte går skulle jag bygga en NodeJS-applikation på en kraftfull burk som klarar av att "parsa" en så stor fil.

Har du kolla på https://dadroit.com/? Kan det vara användbart? (vet inte själv)

Det verkar som en underbar funktion tyvärr har jag bara Postgresql 10 jag har varken inflytande i vilken version som installeras på servern eller hur json filen läses i postgresql

jag måste hitta ett annat sätt att underlätta läsningen.

Tak iaf för ditt tips det är en underbar funktion.

Om någon annan har tips är jag tacksam. Som sagt, kan inte uppgradera postgresql
eller loada filen på annat sätt.

Lite inne på samma spår. Känns väldigt ineffektivt att söka i så stora jsonfiler. Borde inte vara alltför komplicerat att parsa jsonfilen och skapa upp datat i tabeller istället. Med rätt index så borde det gå
mycket snabbare att ställa frågor samt att frågorna blir enklare.

Fast prestandan kanske inte är ett problem.

Ja och det var ju inte svar på frågan om verktyg men ett förslag på annat angreppssätt.

1) Har ingen kontroll över hur data läses in jag får lösa problemet med data som det är
2) Performance är inget problem när man väl byggt upp sql statement, få sekunder
3) Det är VÄLDIGT VANLIGT att man storar hela json filen i en json kolumn:


"One of PostgreSQL's benefits is that it's a relational database, but you can also get the advantages of unstructured data by storing things in a JSON column."
https://popsql.com/learn-sql/postgre...-in-postgresql

Du har alltså redan möjligheten att göra queries mot JSON-kolumnen och vill enbart ha ett verktyg för att skapa dessa queries?

Skriv i så fall bara ett Python-script som du själv är inne på, borde vara en barnlek.

Exactly. Hela json filen är inläst i en postgresql kolumn, tydligen en rätt så vanlig standardmetod.

Jag kan som sagt koda i py eller vba. Vet inte om det är sä piece of cake att få det robust.

Jag tänker att eftersom postgresql är så populär och modern db och json likaså kan jag inte vara första personen i universum som önskar underlätta proceduren.

DET GÅR att konstruera sql statement för hand, man ska hålla tungan rätt i mun och hålla reda på alla element för det blir riktigt stora statements när det är många nivåer. Jag kan inte tänka mig att det inte finns ett verktyg.

Ja, det finns det eleganta json_path_query som någon föreslog men först med postgresql 12 vi har 10.

Men inläsningen av json i postgresql på samma sätt har funnits i flera år innan version 12 släpptes.

Tacksam för fler förslag.

Ett annat förslag är att exportera JSON-objektet till en vanlig JSON-fil och "parsa" programmatiskt i Python. Eftersom filen är så stor lär modulen behövs iJSON behövas: https://pypi.org/project/ijson/
som möjliggör "parsing" av oändligt stora JSON-filer.

Absolut, det är en god tanke, men det kan vi göra redan nu med att läsa 'filen' från en postgresql tabell, det är ju det det är, filen är inläst i sin helhet i tabellens kolumn

Så med py eller vba kan man hämta data och parsa. Tror det får gå den vägen.

Jag hör att i organisationen är en stor historia att uppgradera postgresql från 10 till 12
så vi får fortsätta använda det jobbiga jsonb_array_element (version 10) istället för
det bättre json(b)_path_query tills vi hittar ett bättre alternativ.

Konstigt att postgresql v10 som releasades i Oktober 2017 och hade en hel del json funktioner
men inga som underlättade json inläsning från tabell fram till version 12 i Oktober 2019

Nu finns sedan September 2021 version 14 av postgresql