json to PostgreSQL konvertierung

Allgemeines zu PostgreSQL.
tomalkis
Neuer Benutzer
Beiträge: 25
Registriert: Fr 14. Sep 2018, 11:01
Wohnort: Olching

json to PostgreSQL konvertierung

Beitrag von tomalkis » Mo 5. Nov 2018, 16:07

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

akretschmer
Erfahrener Benutzer
Beiträge: 7513
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: json to PostgreSQL konvertierung

Beitrag von akretschmer » Mo 5. Nov 2018, 16:15

PostgreSQL kann JSON direkt speichern, auch als JSONB, und dann indexbasiert drin suchen. Was genau ist Dein Problem?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Gisman
Erfahrener Benutzer
Beiträge: 1108
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: json to PostgreSQL konvertierung

Beitrag von Gisman » Mo 5. Nov 2018, 19:39

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

tomalkis
Neuer Benutzer
Beiträge: 25
Registriert: Fr 14. Sep 2018, 11:01
Wohnort: Olching

Re: json to PostgreSQL konvertierung

Beitrag von tomalkis » Di 6. Nov 2018, 08:46

akretschmer hat geschrieben:
Mo 5. Nov 2018, 16:15
PostgreSQL kann JSON direkt speichern, auch als JSONB, und dann indexbasiert drin suchen. Was genau ist Dein Problem?
Ich habe zwei große JSON-Dateien und brauche aber zwei Tabellen davon in meiner Datenbank. Die Tabellendaten muss ich vergleichen und da gespeicherte Daten im QGIS visualisieren. Das Problem war, dass ich nicht wusste, wie ich meine JSON in das PostgreSQL Skript umwandeln kann.
Grüße
Tamara.

tomalkis
Neuer Benutzer
Beiträge: 25
Registriert: Fr 14. Sep 2018, 11:01
Wohnort: Olching

Re: json to PostgreSQL konvertierung

Beitrag von tomalkis » Di 6. Nov 2018, 08:47

Gisman hat geschrieben:
Mo 5. Nov 2018, 19:39
Ich lese JSON meist mit einem Python-Skript und schreibe das in die Datenbank.
Mit Python habe ich leider bis jetzt noch nicht gearbeitet. Aber danke für dein Tipp.

Grüße
Tamara.

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: json to PostgreSQL konvertierung

Beitrag von castorp » Di 6. Nov 2018, 08:53

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 (help/bbcode#f2r1) um die Daten zu formatieren
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

tomalkis
Neuer Benutzer
Beiträge: 25
Registriert: Fr 14. Sep 2018, 11:01
Wohnort: Olching

Re: json to PostgreSQL konvertierung

Beitrag von tomalkis » Di 6. Nov 2018, 11:10

Hier ist ein stück von JSON

Code: Alles auswählen

"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.

Code: Alles auswählen

              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        |

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: json to PostgreSQL konvertierung

Beitrag von castorp » Di 6. Nov 2018, 11:41

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)
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

tomalkis
Neuer Benutzer
Beiträge: 25
Registriert: Fr 14. Sep 2018, 11:01
Wohnort: Olching

Re: json to PostgreSQL konvertierung

Beitrag von tomalkis » Di 6. Nov 2018, 12:44

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

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: json to PostgreSQL konvertierung

Beitrag von castorp » Di 6. Nov 2018, 13:17

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.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Antworten