postgresql
JSON समर्थन

परिचय

एक शुद्ध JSON तालिका बनाना

शुद्ध JSON तालिका बनाने के लिए आपको JSONB प्रकार के साथ एक एकल फ़ील्ड प्रदान करने की आवश्यकता है:

CREATE TABLE mytable (data JSONB NOT NULL);

आपको एक मूल सूचकांक भी बनाना चाहिए:

CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);

इस बिंदु पर आप तालिका में डेटा सम्मिलित कर सकते हैं और इसे कुशलतापूर्वक क्वेरी कर सकते हैं।

जटिल JSON दस्तावेज़ों को छोड़ना

तालिका में एक जटिल JSON दस्तावेज़ लेना:

CREATE TABLE mytable (data JSONB NOT NULL);
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);
INSERT INTO mytable VALUES($$
{
    "name": "Alice",
    "emails": [
        "[email protected]",
        "[email protected]"
    ],
    "events": [
        {
            "type": "birthday",
            "date": "1970-01-01"
        },
        {
            "type": "anniversary",
            "date": "2001-05-05"
        }
    ],
    "locations": {
        "home": {
            "city": "London",
            "country": "United Kingdom"
        },
        "work": {
            "city": "Edinburgh",
            "country": "United Kingdom"
        }
    }
}
$$);

शीर्ष-स्तरीय तत्व के लिए प्रश्न:

SELECT data->>'name' FROM mytable WHERE data @> '{"name":"Alice"}';

किसी सरणी में एक साधारण आइटम के लिए क्वेरी:

SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["[email protected]"]}';

किसी सरणी में किसी ऑब्जेक्ट के लिए क्वेरी:

SELECT data->>'name' FROM mytable WHERE data @> '{"events":[{"type":"anniversary"}]}';

नेस्टेड ऑब्जेक्ट के लिए क्वेरी:

SELECT data->>'name' FROM mytable WHERE data @> '{"locations":{"home":{"city":"London"}}}';

-> और ->> की तुलना में @> का प्रदर्शन

क्वेरी के WHERE भाग में @> , -> और ->> का उपयोग करने के बीच के अंतर को समझना महत्वपूर्ण है। यद्यपि ये दोनों प्रश्न मोटे तौर पर समतुल्य प्रतीत होते हैं:

SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
SELECT data FROM mytable WHERE data->>'name' = 'Alice';

पहला स्टेटमेंट ऊपर बनाए गए इंडेक्स का उपयोग करेगा जबकि बाद के दो नहीं, एक पूर्ण टेबल स्कैन की आवश्यकता होगी।

परिणामी डेटा प्राप्त करते समय -> ऑपरेटर का उपयोग करना अभी भी स्वीकार्य है, इसलिए निम्नलिखित प्रश्न सूचकांक का भी उपयोग करेंगे:

SELECT data->'locations'->'work' FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data->'locations'->'work'->>'city' FROM mytable WHERE data @> '{"name":"Alice"}';

JSONb ऑपरेटरों का उपयोग करना

एक DB और एक तालिका बनाना

DROP DATABASE IF EXISTS books_db;
CREATE DATABASE books_db WITH ENCODING='UTF8' TEMPLATE template0;

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

डीबी को आबाद करना

INSERT INTO books(client, data) values (
    'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),(
    'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),(
    'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

तालिका पुस्तकों के अंदर सब कुछ देखने दें:

SELECT * FROM books;

आउटपुट:

-> ऑपरेटर JSON कॉलम से मान लौटाता है

1 कॉलम का चयन:

SELECT client, 
    data->'title' AS title
    FROM books;

आउटपुट:

2 कॉलम का चयन:

SELECT client, 
   data->'title' AS title, data->'author' AS author
   FROM books;

आउटपुट:

-> बनाम ->>

-> ऑपरेटर मूल JSON प्रकार (जो एक वस्तु हो सकती है) लौटाता है, जबकि ->> पाठ लौटाता है।

NESTED ऑब्जेक्ट वापस करें

आप एक नेस्टेड ऑब्जेक्ट को वापस करने के लिए -> का उपयोग कर सकते हैं और इस प्रकार ऑपरेटरों को चेन कर सकते हैं:

SELECT client, 
   data->'author'->'last_name' AS author
   FROM books;

आउटपुट:

छनन

अपने JSON के अंदर एक मूल्य के आधार पर पंक्तियों का चयन करें:

 SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'title' = '"Dharma Bums"';

नोटिस का उपयोग करता है -> इसलिए हमें JSON '"Dharma Bums"' तुलना करनी चाहिए

या हम इस्तेमाल कर सकते हैं ->> और 'Dharma Bums' तुलना करें

आउटपुट:

नेस्टेड फ़िल्टरिंग

किसी नेस्टेड JSON ऑब्जेक्ट के मान के आधार पर पंक्तियाँ खोजें:

SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'author'->>'last_name' = 'Kerouac';

आउटपुट:

एक वास्तविक दुनिया उदाहरण

CREATE TABLE events (
  name varchar(200),
  visitor_id varchar(200),
  properties json,
  browser json
);

हम इस तालिका में पृष्ठदृश्य की तरह ईवेंट संग्रहीत करने जा रहे हैं। प्रत्येक घटना में गुण होते हैं, जो कुछ भी हो सकता है (उदाहरण के लिए वर्तमान पृष्ठ) और ब्राउज़र के बारे में जानकारी भी भेजता है (जैसे ओएस, स्क्रीन रिज़ॉल्यूशन, आदि)। ये दोनों पूरी तरह से स्वतंत्र रूप हैं और समय के साथ बदल सकते हैं (जैसा कि हम ट्रैक करने के लिए अतिरिक्त सामान के बारे में सोचते हैं)।

INSERT INTO events (name, visitor_id, properties, browser) VALUES
(
  'pageview', '1',
  '{ "page": "/" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'pageview', '2',
  '{ "page": "/" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1920, "y": 1200 } }'
),(
  'pageview', '1',
  '{ "page": "/account" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'purchase', '5',
  '{ "amount": 10 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1024, "y": 768 } }'
),(
  'purchase', '15',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),(
  'purchase', '15',
  '{ "amount": 500 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);

अब सब कुछ का चयन करें:

SELECT * FROM events;

आउटपुट:

JSON ऑपरेटरों + PostgreSQL के कुल कार्य

JSON ऑपरेटरों का उपयोग करते हुए, पारंपरिक PostgreSQL कुल कार्यों के साथ संयुक्त, हम जो चाहें बाहर निकाल सकते हैं। आपके पास अपने निपटान में RDBMS की पूरी ताकत हो सकती है।

• ब्राउज़र का उपयोग देखें:

    SELECT browser->>'name' AS browser, 
      count(browser)
      FROM events
      GROUP BY browser->>'name';
  

आउटपुट:

• आगंतुक प्रति कुल राजस्व:

    SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total
    FROM events
    WHERE CAST(properties->>'amount' AS integer) > 0
    GROUP BY visitor_id;
  

आउटपुट:

• औसत स्क्रीन रिज़ॉल्यूशन

    SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,
      AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height
    FROM events;
  

आउटपुट:

अधिक उदाहरण और दस्तावेज यहां और यहां ।