🧚 Άκου! Το Beekeeper Studio είναι ένα γρήγορο, σύγχρονο και open source GUI για PostgreSQL Λήψη
23 Μαρτίου 2025 Από τον Matthew Rathbone

Η PostgreSQL προσφέρει ισχυρή υποστήριξη για αποθήκευση και αναζήτηση δεδομένων JSON μέσω των τύπων δεδομένων json και jsonb. Ο τύπος jsonb είναι ιδιαίτερα χρήσιμος, καθώς αποθηκεύει τα δεδομένα σε δυαδική μορφή, επιτρέποντας πιο αποδοτική ευρετηρίαση και αναζήτηση σε σύγκριση με τον βασισμένο σε κείμενο τύπο json.

Σε αυτόν τον οδηγό θα εξερευνήσουμε τις πιο συνηθισμένες λειτουργίες JSON στην PostgreSQL με πρακτικά παραδείγματα που μπορείς να χρησιμοποιήσεις στις εφαρμογές σου.

Πριν συνεχίσεις το διάβασμα, ήθελα απλώς να σου πω για τον σύγχρονο και γρήγορο διαχειριστή βάσεων δεδομένων PostgreSQL μας, το Beekeeper Studio. Ο SQL editor του κάνει αυτόματη συμπλήρωση καθώς πληκτρολογείς, οπότε δοκιμάζεις αυτούς τους τελεστές JSON γρήγορα, και είναι δωρεάν για PostgreSQL. Κατέβασέ το εδώ.

Δημιουργία δεδομένων JSON

Ας ξεκινήσουμε δημιουργώντας έναν πίνακα με μια στήλη jsonb και εισάγοντας μερικά δείγματα δεδομένων:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    info JSONB
);

-- Insert a customer with basic information
INSERT INTO customers (info) VALUES (
    '{"name": "Alice Smith", "email": "alice@example.com", "age": 32, "addresses": [
        {"type": "home", "city": "San Francisco", "state": "CA"}, 
        {"type": "work", "city": "Oakland", "state": "CA"}
     ]}'
);

-- Insert another customer
INSERT INTO customers (info) VALUES (
    '{"name": "Bob Johnson", "email": "bob@example.com", "age": 45, "addresses": [
        {"type": "home", "city": "Seattle", "state": "WA"}
     ], "orders": [101, 102, 103]}'
);

Βασικές λειτουργίες JSON

Ανάκτηση ολόκληρου του εγγράφου JSON

SELECT info FROM customers WHERE id = 1;

Αποτέλεσμα:

{
  "age": 32,
  "name": "Alice Smith",
  "email": "alice@example.com",
  "addresses": [
    {"city": "San Francisco", "type": "home", "state": "CA"},
    {"city": "Oakland", "type": "work", "state": "CA"}
  ]
}

Εξαγωγή τιμών από JSON

Η PostgreSQL προσφέρει πολλούς τελεστές για την εξαγωγή τιμών από JSON:

Εξαγωγή συγκεκριμένου πεδίου με τον τελεστή -> (επιστρέφει JSON)

-- Get the name field as JSON
SELECT info -> 'name' FROM customers;

Αποτέλεσμα:

"Alice Smith"
"Bob Johnson"

Εξαγωγή συγκεκριμένου πεδίου με τον τελεστή -» (επιστρέφει κείμενο)

-- Get the name field as text
SELECT info ->> 'name' AS customer_name FROM customers;

Αποτέλεσμα:

customer_name
----------------
Alice Smith
Bob Johnson

Πρόσβαση σε ένθετα αντικείμενα

-- Getting the first address (array element 0) city
SELECT info -> 'addresses' -> 0 ->> 'city' AS home_city FROM customers;

Αποτέλεσμα:

home_city
------------
San Francisco
Seattle

Χρήση του τελεστή #> για πρόσβαση μέσω διαδρομής

-- Using the path syntax to get the same value
SELECT info #> '{addresses,0,city}' FROM customers;

Αποτέλεσμα:

"San Francisco"
"Seattle"

Χρήση του τελεστή #» για πρόσβαση μέσω διαδρομής ως κείμενο

-- Using the path syntax to get the same value as text
SELECT info #>> '{addresses,0,city}' AS home_city FROM customers;

Αποτέλεσμα:

home_city
------------
San Francisco
Seattle

Τροποποίηση δεδομένων JSON

Η PostgreSQL παρέχει αρκετές συναρτήσεις για την τροποποίηση δεδομένων JSON:

Προσθήκη πεδίου με jsonb_set

-- Add a new field 'premium_customer' to a customer
UPDATE customers
SET info = jsonb_set(info, '{premium_customer}', 'true')
WHERE id = 1;

Αφαίρεση πεδίου με τον τελεστή -

-- Remove the premium_customer field
UPDATE customers
SET info = info - 'premium_customer'
WHERE id = 1;

Συγχώνευση αντικειμένων JSON με ||

-- Add multiple fields by merging objects
UPDATE customers
SET info = info || '{"status": "active", "last_login": "2025-03-23"}'::jsonb
WHERE id = 1;

Ενημέρωση ένθετου πεδίου

-- Update a nested field (home address state)
UPDATE customers
SET info = jsonb_set(
    info, 
    '{addresses,0,state}', 
    '"NY"'::jsonb
)
WHERE id = 1;

Αναζήτηση σε πίνακες (arrays) JSON

Η εργασία με arrays JSON είναι μια συνηθισμένη λειτουργία:

Λήψη μήκους array

-- Get the number of addresses for each customer
SELECT 
    info ->> 'name' AS customer_name,
    jsonb_array_length(info -> 'addresses') AS address_count
FROM customers;

Αποτέλεσμα:

customer_name  | address_count
---------------+--------------
Alice Smith    | 2
Bob Johnson    | 1

Εξαγωγή συγκεκριμένων στοιχείων array

-- Get the second address (index 1) for Alice
SELECT info -> 'addresses' -> 1 FROM customers WHERE id = 1;

Αποτέλεσμα:

{"city": "Oakland", "type": "work", "state": "CA"}

Ανάπτυξη (unnest) arrays για αναζήτηση μεμονωμένων στοιχείων

-- Unnest the addresses array to query individual addresses
SELECT 
    c.id,
    c.info ->> 'name' AS customer_name,
    jsonb_array_elements(c.info -> 'addresses') AS address
FROM customers c;

Αποτέλεσμα:

id | customer_name | address
---+---------------+------------------------------------------------------
1  | Alice Smith   | {"city": "San Francisco", "type": "home", "state": "NY"}
1  | Alice Smith   | {"city": "Oakland", "type": "work", "state": "CA"}
2  | Bob Johnson   | {"city": "Seattle", "type": "home", "state": "WA"}

Φιλτράρισμα με αναπτυγμένα arrays

-- Find customers with addresses in CA
SELECT DISTINCT
    c.id,
    c.info ->> 'name' AS customer_name
FROM customers c,
     jsonb_array_elements(c.info -> 'addresses') AS addr
WHERE addr ->> 'state' = 'CA';

Αποτέλεσμα:

id | customer_name
---+--------------
1  | Alice Smith

Έλεγχος για κλειδιά και τιμές

Έλεγχος αν υπάρχει ένα κλειδί

-- Find customers who have the 'orders' key
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info ? 'orders';

Αποτέλεσμα:

id | customer_name
---+--------------
2  | Bob Johnson

Έλεγχος για τιμές σε arrays

-- Find customers with a specific order number
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info -> 'orders' ? '101';

Αποτέλεσμα:

id | customer_name
---+--------------
2  | Bob Johnson

Έλεγχος αν ταιριάζει κάποιο στοιχείο του array

-- Find customers who have an address in Oakland (using path operators)
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info @> '{"addresses": [{"city": "Oakland"}]}';

Αποτέλεσμα:

id | customer_name
---+--------------
1  | Alice Smith

Ευρετηρίαση δεδομένων JSON

Για βάσεις δεδομένων παραγωγής με μεγάλα σύνολα δεδομένων JSON, η σωστή ευρετηρίαση είναι απαραίτητη:

Δημιουργία ευρετηρίου GIN για queries περιεχομένου (containment)

-- Create a GIN index for faster containment operations
CREATE INDEX idx_customers_info ON customers USING GIN (info);

Δημιουργία ειδικού ευρετηρίου για ένα πεδίο JSON

-- Create an index on the 'email' field for fast lookups
CREATE INDEX idx_customers_email ON customers ((info ->> 'email'));

Χρήση πεδίων με ευρετήριο σε queries

-- This query can use the index we created above
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info ->> 'email' = 'alice@example.com';

Μετατροπή μεταξύ JSON και πινάκων

Μετατροπή JSON σε γραμμές (jsonb_to_record)

-- Extract JSON fields into a tabular structure
SELECT 
    id,
    rec.name,
    rec.email,
    rec.age
FROM 
    customers,
    jsonb_to_record(info) AS rec(
        name text,
        email text,
        age int
    );

Αποτέλεσμα:

id | name         | email             | age
---+--------------+-------------------+-----
1  | Alice Smith  | alice@example.com | 32
2  | Bob Johnson  | bob@example.com   | 45

Μετατροπή ένθετων arrays σε πίνακες (jsonb_to_recordset)

-- Extract addresses from all customers into a table
SELECT 
    c.id,
    c.info ->> 'name' AS customer_name,
    addr.type AS address_type,
    addr.city,
    addr.state
FROM 
    customers c,
    jsonb_to_recordset(c.info -> 'addresses') AS addr(
        type text,
        city text,
        state text
    );

Αποτέλεσμα:

id | customer_name | address_type | city          | state
---+---------------+--------------+---------------+------
1  | Alice Smith   | home         | San Francisco | NY
1  | Alice Smith   | work         | Oakland       | CA
2  | Bob Johnson   | home         | Seattle       | WA

Μετατροπή αποτελεσμάτων query σε JSON

-- Aggregate customer orders into a JSON array
SELECT
    c.id,
    c.info ->> 'name' AS customer_name,
    json_agg(o.order_id) AS orders
FROM 
    customers c
    CROSS JOIN LATERAL (
        SELECT jsonb_array_elements_text(c.info -> 'orders') AS order_id
    ) o
GROUP BY c.id, c.info ->> 'name';

Αποτέλεσμα:

id | customer_name | orders
---+---------------+----------
2  | Bob Johnson   | ["101", "102", "103"]

Ζητήματα απόδοσης

Όταν δουλεύεις με JSON στην PostgreSQL, να έχεις υπόψη σου τα εξής σχετικά με την απόδοση:

  1. Χρησιμοποίησε jsonb αντί για json: Ο τύπος jsonb προσφέρει καλύτερη απόδοση για τις περισσότερες λειτουργίες.

  2. Φτιάξε ευρετήρια στρατηγικά: Χρησιμοποίησε ευρετήρια GIN για queries περιεχομένου, αλλά δημιούργησε ειδικά ευρετήρια για πεδία που αναζητάς συχνά.

  3. Σκέψου προσεκτικά την αποκανονικοποίηση: Παρόλο που το JSON σου επιτρέπει να αποκανονικοποιήσεις το μοντέλο δεδομένων σου, η υπερβολική ένθεση μπορεί να επηρεάσει την απόδοση των queries.

  4. Πρόσεχε τα μοτίβα των queries: Σύνθετες εκφράσεις διαδρομής JSON ή συχνή ανάπτυξη μεγάλων arrays μπορεί να είναι δαπανηρές λειτουργίες.

  5. Περιόρισε το μέγεθος των arrays: Πολύ μεγάλα arrays JSON μπορεί να προκαλέσουν προβλήματα απόδοσης κατά την ανάπτυξή τους.

  6. Σκέψου την προβολή: Εξήγαγε μόνο τα πεδία που χρειάζεσαι αντί να επιστρέφεις ολόκληρο το έγγραφο JSON.

Συμπέρασμα

Οι δυνατότητες JSON της PostgreSQL την κάνουν εξαιρετική επιλογή για εφαρμογές που χρειάζεται να αποθηκεύουν και να αναζητούν ημιδομημένα δεδομένα. Με τους τελεστές και τις συναρτήσεις αυτού του οδηγού, μπορείς να δουλεύεις αποτελεσματικά με δεδομένα JSON, αξιοποιώντας παράλληλα τη δύναμη και την αξιοπιστία μιας ώριμης σχεσιακής βάσης δεδομένων.

Για σύνθετα μοντέλα δεδομένων, σκέψου μια υβριδική προσέγγιση: χρησιμοποίησε δομημένους πίνακες για σταθερά σχήματα και δεδομένα που αναζητάς συχνά, και JSON για ευέλικτα δεδομένα χωρίς σχήμα ή ένθετες δομές που θα ήταν άβολο να μοντελοποιηθούν σχεσιακά.

Έτοιμος να δοκιμάσεις αυτά τα παραδείγματα μόνος σου; Άνοιξε το Beekeeper Studio και ξεκίνα να εξερευνάς τις δυνατότητες JSON της PostgreSQL σήμερα.

Το Beekeeper Studio είναι ένα δωρεάν & open source GUI για PostgreSQL

Αυτός είναι ο PostgreSQL client που έψαχνα! Το UI είναι αυτό που τον ξεχωρίζει: καθαρό, εύχρηστο, ισχυρό. Αντίο, δύσχρηστο PGAdmin. - David Cyprian

Το Beekeeper Studio είναι ένας γρήγορος, εύχρηστος και απλός PostgreSQL client. Υποστηρίζει επίσης πολλές άλλες βάσεις δεδομένων και λειτουργεί άψογα σε Windows, Mac και Linux.

Η έκδοση του Beekeeper για Linux έχει 100% όλες τις λειτουργίες, χωρίς εκπτώσεις και χωρίς συμβιβασμούς.

Τι λένε οι χρήστες της PostgreSQL για το Beekeeper Studio

Jay Simons Jay Simons Ιδιοκτήτης, Designly.biz

Σε αντίθεση με το pgAdmin, το Beekeeper είναι εντυπωσιακά απλό στη χρήση χωρίς να του λείπουν δυνατότητες–και η τιμή είναι σωστή! Η λειτουργία backup της βάσης δεδομένων είναι ιδιαίτερα χρήσιμη. Απλώς εξαιρετικό λογισμικό.

Alex M

Καταπληκτικό εργαλείο, το πιο πρακτικό IDE που έχω βρει μέχρι σήμερα για διαχείριση βάσεων Postgres και MySql - απλώς δουλεύει, χωρίς φασαρία :-)

Έτοιμος να βελτιώσεις τη ροή εργασίας σου στην PostgreSQL;

download Κατέβασε δωρεάν