json to PostgreSQL konvertierung

Hallo,

kennt jemand ein Tool für die Konvertierung json -> PostgreSQL. Bis jetzt habe ich sqlizer.io benutzt. Aber für die große JSON (>5000 rows) ist das leider nicht mehr kostenlos.

Grüße
Tamara

PostgreSQL kann JSON direkt speichern, auch als JSONB, und dann indexbasiert drin suchen. Was genau ist Dein Problem?

Ich lese JSON meist mit einem Python-Skript und schreibe das in die Datenbank.

Wie sieht denn das JSON aus? Wie genau soll das denn importiert werden?

Kannst Du uns ein (verkürztes) Beispiel der JSON Datei zeigen und die Tabellendefinition in die Datensätze importiert werden sollen?

Bitte verwende [code] tags (https://www.pg-forum.de/help/bbcode#f2r1) um die Daten zu formatieren

Hier ist ein stück von JSON

"copyright": "Copyright",
    "versionNumber": "777",
    "createdDate": "2018-11-05T13:20:04Z",
    "responseId": "7tt7uu77",
    "result": [
        {
            "currencyIso": "USD",
            "occupancy": {
                "probability": 51,
                "pct": 70,
                "rank": 1,
                "bucket": 2,
                "available": null
            },
            "note": "",
            "url": "https://www.parkme.com/lot/16929/1829-1st-avenue",
            "phone": "(212) 722-2242",
            "spacesTotal": 36,
            "reviewScore": 0,
            "pmtTypes": [
                "Amex",
                "Banknoten",
                "Münzen",
                "Debit Card",
                "Discover",
                "Mc/Visa",
                "Bezahlen Per Telefon"
            ],
            "hrs": [
                "Mo-So: 24 Stunden"
            ],
            "structured_rates": [
                {
                    "dow_start": 1,
                    "dow_end": 7,
                    "rate": 12,
                    "increment": 30
                },
                {
                    "dow_start": 1,
                    "dow_end": 7,
                    "rate": 20.01,
                    "increment": 60
                },
                {
                    "dow_start": 1,
                    "dow_end": 7,
                    "rate": 25,
                    "increment": 120
                },
                {
                    "dow_start": 1,
                    "dow_end": 7,
                    "rate": 35,
                    "increment": 600
                },
                {
                    "dow_start": 1,
                    "dow_end": 7,
                    "rate": 44.99,
                    "increment": 1440,
                    "repeats": true
                },
                {
                    "time_in": "06:00-10:00",
                    "time_out": "06:00-19:00",
                    "dow_start": 1,
                    "dow_end": 7,
                    "rate": 17
                },
                {
                    "dow_start": 1,
                    "dow_end": 7,
                    "rate": 1200,
                    "increment": 44640,
                    "repeats": true
                }
            ],
            "currency": "$",
            "pois": [],
            "reviewCount": 0,
            "ev_stations": [],
            "amenities": [
                {
                    "id": 9,
                    "name": "Covered Parking Available",
                    "value": true
                },
                {
                    "id": 10,
                    "name": "Open 24/7",
                    "value": true
                },
                {
                    "id": 13,
                    "name": "Unobstructed Parking",
                    "value": true
                },
                {
                    "id": 18,
                    "name": "Credit Cards Accepted",
                    "value": true
                },
                {
                    "id": 25,
                    "name": "Parking Lighting",
                    "value": true
                }
            ],
            "rateCard": [
                "30 Min: $10.14",
                "1 Stunde: $16.90",
                "2 Stunden: $21.12",
                "10 Stunden: $29.57",
                "24 Stunden: $38.01",
                "Übergröße zusätzlich: $12.67",
                "Frühaufsteher (in 6am-10am / Abfahrt um 7pm): $14.36",
                "Monatlich: $591.34-$1013.73",
                "Steuern Nicht Enthalten: 18.375%"
            ],
            "paymentIDs": [],
            "format": "Parkhaus",
            "photoThumbs": [
                "https://d13esfgglb25od.cloudfront.net/lot_img/16929/3a07709c53424e4094947e28c85931cc_thumb.jpg",
                "https://d13esfgglb25od.cloudfront.net/icon.png"
            ],
            "operator": "Icon Parking",
            "reservations": [
                {
                    "link": "https://www.parkme.com/lot/16929/1829-1st-avenue",
                    "source": "ParkMe",
                    "monthly": false
                }
            ],
            "id": 16929,
            "navigationAddress": {
                "street": "1829 1st Avenue",
                "city": "NYC",
                "state": "New York",
                "postal": "10128",
                "country": "US",
                "type": "NAVIGATION"
            },
            "handicapSpacesTotal": 0,
            "name": "Parkhaus 1829 1st Avenue",
            "type": "Valet Only",
            "peps": [
                {
                    "pepPt": [
                        -73.9455232632,
                        40.7824301671
                    ],
                    "pepAz": 284,
                    "pepPrimary": null,
                    "pepType": "IN"
                }
            ],
            "costIndex": 2,
            "distance": 9480,
            "buildingAddress": {
                "street": "1829 1st Avenue",
                "city": "NYC",
                "state": "New York",
                "postal": "10128",
                "country": "US",
                "type": "LEGAL"
            },
            "point": {
                "type": "Point",
                "coordinates": [
                    -73.9457009,
                    40.7824722
                ]
            },
            "polygon": "mj|wFdpibMX{@b@XWz@e@Y??",
            "phones": [
                {
                    "type": "Lot",
                    "number": "(212) 722-2242"
                },
                {
                    "type": "Central Office",
                    "number": "(877) 727-5464"
                },
                {
                    "type": "Monthly",
                    "number": "(888) 705-7845"
                }
            ],
            "calculatedRates": null,
            "isOpen": null,
            "reviews": [],
            "photos": [
                {
                    "thumbnail": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/3a07709c53424e4094947e28c85931cc_thumb.jpg",
                    "assetTypes": [
                        "Entrance",
                        "Rates"
                    ],
                    "full": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/3a07709c53424e4094947e28c85931cc.jpg",
                    "medium": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/3a07709c53424e4094947e28c85931cc_medium.jpg"
                },
                {
                    "thumbnail": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/454c7a8f7e874f2db075028513e30af9_thumb.jpg",
                    "assetTypes": [
                        "Rates",
                        "Hours",
                        "Phone",
                        "Operator",
                        "Payment Types"
                    ],
                    "full": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/454c7a8f7e874f2db075028513e30af9.jpg",
                    "medium": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/454c7a8f7e874f2db075028513e30af9_medium.jpg"
                },
                {
                    "thumbnail": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/289d4905d8404cd1aef1c81ac2dd3dc4_thumb.jpg",
                    "assetTypes": [
                        "Rates",
                        "Operator",
                        "Location"
                    ],
                    "full": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/289d4905d8404cd1aef1c81ac2dd3dc4.jpg",
                    "medium": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/289d4905d8404cd1aef1c81ac2dd3dc4_medium.jpg"
                },
                {
                    "thumbnail": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/fc74f85df88f4c758bab9716002aba4c_thumb.jpg",
                    "assetTypes": [
                        "Rates",
                        "Hours",
                        "Operator",
                        "Location"
                    ],
                    "full": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/fc74f85df88f4c758bab9716002aba4c.jpg",
                    "medium": "https://d13esfgglb25od.cloudfront.net/lot_img/16929/fc74f85df88f4c758bab9716002aba4c_medium.jpg"
                },
                {
                    "thumbnail": "https://d13esfgglb25od.cloudfront.net/icon.png",
                    "assetTypes": [
                        "OPERATOR_LOGO"
                    ],
                    "full": "https://d13esfgglb25od.cloudfront.net/icon.png",
                    "medium": "https://d13esfgglb25od.cloudfront.net/icon.png"
                }
            ],
            "offset": -18000,
            "tz": "EST"
        },

Hier ist die Tabelle, die ich von dem JSON mit anderen Daten drinnen (deutlich weniger Daten) bereits auf der Seite sqlizer.io erzeugt habe.

              Spalte               |      Typ       | Attribute
------------------------------------+----------------+-----------
 copyright                          | text           |
 versionNumber                      | numeric(2,1)   |
 createdDate                        | text           |
 responseId                         | text           |
 result_pois_type                   | text           |
 result_pois_poi_name               | text           |
 result_pois_coordinates            | numeric(9,7)   |
 result_reviewCount                 | integer        |
 result_hrs                         | text           |
 result_reviewScore                 | integer        |
 result_amenities_id                | integer        |
 result_amenities_name              | text           |
 result_amenities_value             | text           |
 result_peps_pepPt                  | numeric(12,10) |
 result_peps_pepAz                  | integer        |
 result_peps_pepPrimary             | integer        |
 result_peps_pepType                | text           |
 result_handicapSpacesTotal         | integer        |
 result_navigationAddress_city      | text           |
 result_navigationAddress_type      | text           |
 result_navigationAddress_state     | text           |
 result_navigationAddress_postal    | integer        |
 result_navigationAddress_street    | text           |
 result_navigationAddress_country   | text           |
 result_type                        | text           |
 result_spacesTotal                 | integer        |
 result_phone                       | text           |
 result_name                        | text           |
 result_photoThumbs                 | text           |
 result_currency                    | text           |
 result_note                        | integer        |
 result_rateCard                    | text           |
 result_structured_rates_rate       | numeric(5,2)   |
 result_structured_rates_dow_end    | integer        |
 result_structured_rates_repeats    | text           |
 result_structured_rates_dow_start  | integer        |
 result_structured_rates_increment  | integer        |
 result_operator                    | text           |
 result_id                          | integer        |
 result_url                         | text           |
 result_format                      | text           |
 result_currencyIso                 | text           |
 result_costIndex                   | integer        |
 result_distance                    | integer        |
 result_buildingAddress_city        | text           |
 result_buildingAddress_type        | text           |
 result_buildingAddress_state       | text           |
 result_buildingAddress_postal      | integer        |
 result_buildingAddress_street      | text           |
 result_buildingAddress_country     | text           |
 result_point_type                  | text           |
 result_point_coordinates           | numeric(12,10) |
 result_polygon                     | text           |
 result_href                        | text           |
 result_phones_type                 | text           |
 result_phones_number               | text           |
 result_calculatedRates             | integer        |
 result_occupancy_available         | integer        |
 result_occupancy_start             | text           |
 result_occupancy_bucket            | integer        |
 result_occupancy_probability       | integer        |
 result_occupancy_pct               | integer        |
 result_occupancy_rank              | numeric(2,1)   |
 result_occupancyPct_occPct         | integer        |
 result_occupancyPct_occAvail       | integer        |
 result_occupancyPct_occFull        | integer        |
 result_occupancyPct_occStartDate   | text           |
 result_occupancyPct_occIndex       | integer        |
 result_isOpen                      | integer        |
 result_photos_thumbnail            | text           |
 result_photos_assetTypes           | text           |
 result_photos_full                 | text           |
 result_photos_medium               | text           |
 result_offset                      | numeric(5,1)   |
 result_tz                          | text           |
 result_pmtTypes                    | text           |
 result_structured_rates_time_in    | text           |
 result_amenities_metric            | numeric(14,10) |
 result_structured_rates_time_out   | text           |
 result_structured_rates_max        | integer        |
 result_structured_rates_additional | integer        |
 result_occupancy                   | integer        |
 result_occupancyPct                | integer        |
 result_reviews_reviewDate          | text           |
 result_reviews_name                | text           |
 result_reviews_review              | text           |
 result_reviews_stars               | integer        |

Hmm, da scheint es mehr als eine “result” pro “copyright” zu geben (da “result” ein JSON array ist) - wie soll dass denn importiert werden? Was passiert denn wenn es mehr als einen Eintrag in dem “result” array gibt? Werden dann die Kopfdaten wiederholt? Sprich: gibt es dann Duplikate in der copyright Spalte der Zieltabelle? Ich hätte ja erwartet, dass es eine “copyright” Tabelle gibt und eine “result” Tabelle die eine 1-to-many Verknüpfung haben.

Gleiches gilt für die Schlüssel: “structured_rates”, “amenities”, “phones”, “photos”, “reviews”, “peps”, “reservations”, “paymentIDs”, “rateCard”, “pois”, “ev_stations”, “hrs”, “pmtTypes” - das sind alles Arrays mit teilweise mehr als einem Eintrag. Auch da müsste man eigentlich eine 1-to-many Verknüpfung haben (zur “result” Tabelle). Oder alternativ die Arrays als JSON array speichern.

Z.B. nur eine Spalte “result_structured_rates” vom Typ JSONB, indem alle Rates als JSON Objekte abgelegt sind - dann müssten aber die anderen Spalten die mit “result_structured_rates” anfangen, gelöscht werden (analog für alle anderen Arrays in dem JSON)

es gibt nur eine “result”, genau wie nur eine “copyright”. Die ganzen Daten, die ich brauche sind in result array drinnen.

Wenn es nur ein “result” gibt, stellt sich mir die Frage warum es ein Array ist.

Aber dann bleiben immer noch die “structured_rates”, “photos”, “phones” und “reviews” - was soll mit denen passieren? Zumindes für “structured_rates”, “photos” und “phones” sind auch in Deinem Beispiel mehrfach Werte vorhanden.

Ich habe deine Fragen leider nicht ganz verstanden, da ich die Konvertierung nicht per Hand gemacht habe. Nach der Konvertierung sah die Tabelle so aus und ich konnte mit den Daten, die ich brauchte, gut arbeiten. Wie du sagst wurden die mehreren Daten wiederholt, aber ich habe immer nur die rausgeholt, die ich gerade gebraucht habe. Von “result_structured_rates” brauche ich alle “result_structured_rates_rate” und “result_structured_rates_increment”. Diese habe ich so rausgeholt:
select string_agg(DISTINCT “result_structured_rates_rate” || ’ at ’ || “result_structured_rates_increment” || ’ minutes’,’, ')…

Eigentlich müsste es reichen, wenn Du ein Stück JSON in eine Spalte reinbekommst (import), viele stücke json also in viele datensätze.
Den Inhalt dieser Spalte kannst Du nach Lust und Laune mit JSON Funktionen abfragen, zerlegen, etc.
Dafür musst Du nicht mal eine neue Tabelle anlegen.

Ab gewissen Datenmenge bietet es sich sicher an, roh importiertes JSON in eine geeignetet strukturierte Tabelle zu überführen, sodass man indizieren kann usw., aber bei 5000 DS wäre das m.E. nicht unbedingt nötig.