normalization - जब मैं SQL तालिका में JSON या XML डेटा सहेज सकता हूं




structured-data (8)

SQL या MySQL (या उस मामले के लिए किसी भी संबंधपरक DB) का उपयोग करते समय - मैं समझता हूं कि नियमित कॉलम में डेटा को बचाने के लिए खातिर अनुक्रमण और अन्य उद्देश्यों के लिए बेहतर है ...

JSON डेटा लोड करने और सहेजने की बात कभी-कभी बहुत अधिक सरल होती है। और विकास को आसान बनाता है।

क्या DB में कच्चे JSON डेटा को बचाने के लिए कोई "सुनहरे नियम" हैं?

क्या ऐसा करना बिल्कुल गलत प्रथा है?

सारांश

बहुत अच्छे उत्तर दिए गए, लेकिन इसमें कोई शक नहीं कि सबसे अच्छी तरह से आयोजित @Shnugo द्वारा दिया गया उत्तर है जो इनाम के योग्य है।

अन्य विशेष उपयोग मामलों की व्याख्या के लिए @Gordon Linoff और @Amresh पांडे द्वारा दिए गए उत्तरों को इंगित करना चाहेंगे।

भगवान का शुक्र है, और सभी को अच्छी नौकरी!


Answers

यह एक टिप्पणी के लिए बहुत लंबा है।

यदि यह "बिल्कुल गलत" था, तो अधिकांश डेटाबेस इसका समर्थन नहीं करेंगे। ठीक है, अधिकांश डेटाबेस FROM क्लॉज में कॉमा का समर्थन करते हैं और मुझे लगता है कि "बिल्कुल गलत" के रूप में। लेकिन JSON के लिए समर्थन नया विकास है, न कि पिछड़े-संगत "फीचर" के लिए।

एक स्पष्ट मामला यह है कि जब JSON संरचना केवल एक BLOB है जो अनुप्रयोग में वापस पारित हो जाती है। फिर कोई बहस नहीं है - अन्य तो JSON के भंडारण का ओवरहेड, जो हर रिकॉर्ड में सामान्य क्षेत्रों के साथ संरचित डेटा के लिए अनावश्यक रूप से क्रिया है।

एक और मामला "विरल" कॉलम का मामला है। आपके पास कई संभावित स्तंभों के साथ पंक्तियाँ हैं, लेकिन ये पंक्ति से पंक्ति में भिन्न होती हैं।

एक और मामला है जब आप रिकॉर्ड में "नेस्टेड" रिकॉर्ड संग्रहीत करना चाहते हैं। JSON शक्तिशाली है।

यदि JSON के रिकॉर्ड में सामान्य फ़ील्ड हैं, जिस पर आप क्वेरी करना चाहते हैं, तो आप आमतौर पर उचित डेटाबेस कॉलम में इन्हें रखना बेहतर समझते हैं। हालाँकि, डेटा जटिल है और JSON जैसे प्रारूपों के लिए एक जगह है।


प्रश्न आपको पूछना है:

क्या मैं केवल इस डेटाबेस का उपयोग करने के लिए बंधा हूँ?

करना

  1. यदि आप JSON को संग्रहीत करने के लिए किसी भिन्न डेटाबेस का उपयोग कर सकते हैं, तो CouchDB, DynamoDB या MongoDB जैसे दस्तावेज़ संग्रहण समाधान का उपयोग करें।
  2. इन दस्तावेज़ भंडारण DB की क्षमता को अनुक्रमित करने और पदानुक्रम डेटा को खोजने के लिए उपयोग करें।
  3. अपने रिलेशनल डेटा के लिए एक रिलेशनल डेटाबेस का उपयोग करें।
  4. रिपोर्टिंग, डेटा वेयरहाउसिंग और डेटा माइनिंग के लिए एक रिलेशनल डेटाबेस का उपयोग करें।

न करें

  1. यदि संभव हो तो स्ट्रिंग के रूप में JSON स्टोर करें।
  2. कोशिश करें और JSON डेटा की अधिकतम लंबाई के साथ आएं।
  3. JSON को स्टोर करने के लिए varchar का उपयोग करें (यदि आपको आवश्यक हो तो टेक्स्ट / ब्लॉब का उपयोग करें)।
  4. मानों के लिए संग्रहीत JSON के माध्यम से प्रयास करें और खोजें।
  5. स्ट्रिंग के रूप में स्टोर करने के लिए JSON से बचने के बारे में चिंता करें।

PostgreSQL में एक अंतर्निहित json और jsonb डेटा प्रकार है

ये कुछ उदाहरण हैं:

CREATE TABLE orders (
 ID serial NOT NULL PRIMARY KEY,
 info json NOT NULL
);

INSERT INTO orders (info)
VALUES
 (
 '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
 ),
 (
 '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
 ),
 (
 '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
 );

PostgreSQL JSON डेटा को क्वेरी करने के लिए दो मूल ऑपरेटर -> और ->> प्रदान करता है।

ऑपरेटर -> कुंजी द्वारा JSON ऑब्जेक्ट फ़ील्ड लौटाता है।

ऑपरेटर ->> पाठ द्वारा JSON ऑब्जेक्ट फ़ील्ड लौटाता है।

SELECT
 info -> 'customer' AS customer
FROM
 orders;

SELECT
 info ->> 'customer' AS customer
FROM
 orders
WHERE
 info -> 'items' ->> 'product' = 'Diaper'

Json संबंधपरक db में महान नहीं हैं। यदि आप json को कॉलम में स्टोर करते हैं और db में स्टोर करते हैं, तो यह बहुत अच्छा है, लेकिन एक jb को एक blob के रूप में संग्रहीत करना डेटा आर्काइव सिस्टम के रूप में उपयोग करने के लिए है।

किसी जोंस को अनफॉलो न करने और इसे एक ही कॉलम में स्टोर करने के कई कारण हो सकते हैं, लेकिन यह निर्णय लिया गया होगा क्योंकि किसी भी क्वेरी (या मान को पहले से ही कॉलम में अनफॉलो कर दिया गया है) के लिए उस जॉन्स फ़ील्ड के मान का उपयोग नहीं किया जाएगा।

इसके अलावा, अगर सभी क्षेत्र में क्वेरसन प्रसंस्करण का अधिकांश हिस्सा वर्गीय वातावरण के बाहर होगा क्योंकि sql सिर्फ json प्रसंस्करण के लिए नहीं है। असली सवाल तो यह हो जाता है कि मैं इस जसन को कहां स्टोर करता हूं, क्या मैं इसे फ्लैट फाइलों के रूप में बताता हूं और जब आवश्यक हो तो किसी अन्य सिस्टम (स्पार्क / हाइव / आदि) के माध्यम से क्वेरी करें।

मैं आपके DB कलाकार से सहमत हूँ, अभिलेखीय के लिए RDBMS का उपयोग न करें। सस्ते विकल्प हैं। इसके अलावा json blobs बहुत बड़ा हो सकता है और समय के साथ DB डिस्क स्थान को बंद करना शुरू कर सकता है।


मैं अपनी जादू की छड़ी लहराऊंगा। Poof! JSON के उपयोग पर स्वर्ण नियम:

  • यदि MySQL को JSON के अंदर देखने की आवश्यकता नहीं है, और एप्लिकेशन को बस सामान के संग्रह की आवश्यकता है, तो JSON ठीक है, संभवतः बेहतर भी।

  • यदि आप उस डेटा पर खोज करेंगे जो आपके अंदर है और आपके पास MariaDB 10.0.1 या MySQL 5.7 (JSON डेटाटाइप और फ़ंक्शंस के साथ) है, तो JSON व्यावहारिक हो सकता है। MariaDB 5.3 का "डायनामिक" कॉलम इस पर एक प्रकार है।

  • यदि आप "एंटिटी-एट्रीब्यूट-वैल्यू" सामान कर रहे हैं, तो JSON अच्छा नहीं है, लेकिन यह कई बुराइयों में से सबसे कम है। http://mysql.rjweb.org/doc.php/eav

  • अनुक्रमित स्तंभ द्वारा खोज करने के लिए, JSON के अंदर दफन मूल्य नहीं होना एक बड़ा प्लस है।

  • अनुक्रमित स्तंभ, या SPATIAL खोज या स्थान पर किसी श्रेणी द्वारा खोज के लिए, JSON संभव नहीं है।

  • WHERE a=1 AND b=2 लिए "मिश्रित" सूचकांक INDEX(a,b) महान है; शायद JSON के साथ करीब नहीं आ सकता।

  • JSON "विरल" डेटा के साथ अच्छी तरह से काम करता है; काम करता है, लेकिन ऐसा भी नहीं है। (मैं उन मानों का उल्लेख कर रहा हूँ जो कई पंक्तियों के लिए 'गायब' या NULL हैं।)

  • JSON आपको अतिरिक्त तालिका (नों) का सहारा लिए बिना "एरे" और "ट्री" दे सकता है। लेकिन ऐसे सरणियों / पेड़ों में खुदाई केवल ऐप में होती है, एसक्यूएल में नहीं

  • JSON XML से बेहतर दुनिया है। (मेरी राय)

  • यदि आप एप्लिकेशन को छोड़कर JSON स्ट्रिंग में नहीं आना चाहते हैं, तो मैं अनुशंसा करता हूं कि वह (क्लाइंट में) इसे एक BLOB में संग्रहित किया जाए। इसे एक .jpg की तरह सोचें - इसमें सामान है, लेकिन SQL परवाह नहीं करता है।

अपना आवेदन बताएं; शायद हम और अधिक विशिष्ट हो सकते हैं।


"गोल्डन रूल" जो मैं हाथ से लहराने वाले तरीके से इस्तेमाल करता हूं, वह यह है कि अगर मुझे इसके कच्चे प्रारूप में JSON की आवश्यकता है, तो इसे स्टोर करना ठीक है। यदि मुझे इसे पार्स करने का एक विशेष बिंदु बनाना है, तो यह नहीं है।

उदाहरण के लिए, यदि मैं एक एपीआई बना रहा हूं जो कच्चे JSON को भेजता है, और जिस भी कारण से यह मान नहीं बदल रहा है, तो उसे कच्चे JSON के रूप में संग्रहीत करना ठीक है। अगर मुझे इसे पार्स करना है, तो इसे बदलें, इसे अपडेट करें, आदि ... तो इतना नहीं।


नया SQL सर्वर JSON टेक्स्ट को संसाधित करने के लिए फ़ंक्शन प्रदान करता है। JSON के रूप में स्वरूपित जानकारी को मानक SQL सर्वर कॉलम में पाठ के रूप में संग्रहीत किया जा सकता है और SQL सर्वर ऐसे फ़ंक्शन प्रदान करता है जो इन JSON ऑब्जेक्ट्स से मान पुनः प्राप्त कर सकते हैं।

    DROP TABLE IF EXISTS Person

 CREATE TABLE Person 
 ( _id int identity constraint PK_JSON_ID primary key,
 value nvarchar(max)
 CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON(value)>0 )
 )

यह सरल संरचना मानक NoSQL संग्रह के समान है जिसे आप NoSQL डेटाबेस (जैसे Azure DocumentDB या MongoDB) में बना सकते हैं, जहाँ आपके पास बस कुंजी होती है जो JSON का प्रतिनिधित्व करने वाली ID और मान का प्रतिनिधित्व करती है।

ध्यान दें कि NVARCHAR केवल एक सादा पाठ नहीं है। SQL सर्वर में अंतर्निहित टेक्स्ट कंप्रेशन तंत्र है जो डिस्क पर संग्रहीत डेटा को पारदर्शी रूप से संपीड़ित कर सकता है। संपीड़न भाषा पर निर्भर करता है और आपके डेटा (यूनिकोड संपीड़न देखें) के आधार पर 50% तक जा सकता है।

SQL सर्वर और अन्य सादे NoSQL डेटाबेस के बीच मुख्य अंतर यह है कि SQL सर्वर आपको हाइब्रिड डेटा मॉडल का उपयोग करने में सक्षम बनाता है जहां आप एक ही "संग्रह" में कई JSON ऑब्जेक्ट्स को संग्रहीत कर सकते हैं और उन्हें नियमित रिलेशनल कॉलम के साथ जोड़ सकते हैं।

एक उदाहरण के रूप में, कल्पना करें कि हम जानते हैं कि आपके संग्रह के प्रत्येक व्यक्ति के पास FirstName और LastName होगा, और आप उस व्यक्ति के बारे में सामान्य जानकारी को एक JSON ऑब्जेक्ट के रूप में संग्रहीत कर सकते हैं, और अलग-अलग ऑब्जेक्ट के रूप में फ़ोन नंबर / ईमेल पते। SQL सर्वर 2016 में हम आसानी से बिना किसी अतिरिक्त सिंटैक्स के इस संरचना को बना सकते हैं:

DROP TABLE IF EXISTS Person

CREATE TABLE Person (

 PersonID int IDENTITY PRIMARY KEY,

 FirstName nvarchar(100) NOT NULL,

 LastName nvarchar(100) NOT NULL,

 AdditionalInfo nvarchar(max) NULL,

 PhoneNumbers nvarchar(max) NULL,

 EmailAddresses nvarchar(max) NULL
 CONSTRAINT [Email addresses must be formatted as JSON array]
 CHECK ( ISJSON(EmailAddresses)>0 )

 )

एकल JSON ऑब्जेक्ट के बजाय आप इस "संग्रह" में अपना डेटा व्यवस्थित कर सकते हैं। यदि आप प्रत्येक JSON कॉलम की संरचना की स्पष्ट रूप से जांच नहीं करना चाहते हैं, तो आपको हर कॉलम पर JSON चेक बाधा जोड़ने की आवश्यकता नहीं है (इस उदाहरण में मैंने केवल CHECK constraint को EmailAddresses कॉलम पर जोड़ा है)।

यदि आप इस संरचना की मानक NoSQL संग्रह से तुलना करते हैं, तो आप नोटिस कर सकते हैं कि आपके पास दृढ़ता से टाइप किए गए डेटा (FirstName और LastName) तक तेजी से पहुंच होगी। इसलिए, यह समाधान हाइब्रिड मॉडल के लिए अच्छा विकल्प है, जहां आप कुछ ऐसी जानकारी की पहचान कर सकते हैं जो सभी वस्तुओं में दोहराई जाती हैं, और अन्य चर जानकारी को एडसन के रूप में संग्रहीत किया जा सकता है। इस तरह, आप लचीलेपन और प्रदर्शन को जोड़ सकते हैं।

यदि आप इस संरचना की तुलना पर्सन टेबल एडवेंचरवर्क्स डेटाबेस के स्कीमा से करते हैं, तो आप देख सकते हैं कि हमने कई संबंधित टेबलों को हटा दिया है।

स्कीमा की सादगी के अलावा, जटिल रिलेशनल संरचना की तुलना में आपके डेटा एक्सेस ऑपरेशन सरल होंगे। अब आप कई तालिकाओं में शामिल होने के बजाय एकल तालिका पढ़ सकते हैं। जब आपको संबंधित जानकारी (ईमेल पते, फोन नंबर) के साथ नए व्यक्ति को सम्मिलित करने की आवश्यकता होती है, तो आप AdventureWorks व्यक्ति तालिका में एक रिकॉर्ड सम्मिलित करने के बजाय एक तालिका में एक एकल रिकॉर्ड सम्मिलित कर सकते हैं, विदेशी कुंजी खोजने के लिए पहचान कॉलम ले रहे हैं जो फोन को स्टोर करने के लिए उपयोग किया जाएगा। , ईमेल पते, आदि इसके अलावा, इस मॉडल में आप आसानी से विदेशी कुंजी संबंधों का उपयोग करके बिना कैस्केड हटाए एकल व्यक्ति पंक्ति को हटा सकते हैं।

NoSQL डेटाबेस सरल, पढ़ने, सम्मिलित करने और ऑपरेशन को हटाने के लिए अनुकूलित हैं - SQL Server 2016 आपको रिलेशनल डेटाबेस में समान तर्क को लागू करने में सक्षम बनाता है।

JSON बाधाएं पिछले उदाहरणों में, हमने देखा है कि कैसे सरल बाधा को जोड़ना है जो यह पुष्टि करता है कि कॉलम में संग्रहीत पाठ ठीक से स्वरूपित है। यद्यपि JSON के पास मजबूत स्कीमा नहीं है, आप JSON और मानक T-SQL फ़ंक्शन से पढ़ने वाले फ़ंक्शंस को जोड़कर जटिल बाधाओं को भी जोड़ सकते हैं:

ALTER TABLE Person
 ADD CONSTRAINT [Age should be number]
 CHECK ( ISNUMERIC(JSON_VALUE(value, '$.age'))>0 )

 ALTER TABLE Person
 ADD CONSTRAINT [Person should have skills]
 CHECK ( JSON_QUERY(value, '$.skills') IS NOT NULL)
First constraint will take the value of $.age property and check is this numeric value. Second constraint will try to find JSON object in $.skills property and verify that it exists. The following INSERT statements will fail due to the violation of constraints:



INSERT INTO Person(value)
 VALUES ('{"age": "not a number", "skills":[]}')

 INSERT INTO Person(value)
 VALUES ('{"age": 35}')

ध्यान दें कि CHECK की बाधाएं आपके सम्मिलित / अद्यतन प्रक्रियाओं को धीमा कर सकती हैं ताकि आप तेजी से लिखने के प्रदर्शन की आवश्यकता होने पर उनसे बच सकें।

संपीड़ित JSON संग्रहण यदि आपके पास बड़ा JSON टेक्स्ट है, तो आप अंतर्निहित COMPRESS फ़ंक्शन का उपयोग करके स्पष्ट रूप से JSON पाठ को संपीड़ित कर सकते हैं। निम्न उदाहरण में संकुचित JSON सामग्री को बाइनरी डेटा के रूप में संग्रहीत किया जाता है, और हमारे पास गणना कॉलम है जो DECSONPRESS फ़ंक्शन का उपयोग करके JSON को मूल पाठ के रूप में डिकम्प्रेस करता है:

CREATE TABLE Person

 ( _id int identity constraint PK_JSON_ID primary key,

 data varbinary(max),

 value AS CAST(DECOMPRESS(data) AS nvarchar(max))

 )



 INSERT INTO Person(data)

 VALUES (COMPRESS(@json))

COMPRESS और DECOMPRESS फ़ंक्शन मानक GZip संपीड़न का उपयोग करते हैं। यदि आपका क्लाइंट GZip संपीड़न (उदाहरण के लिए ब्राउज़र जो gzip सामग्री को समझता है) को संभाल सकता है, तो आप सीधे संपीड़ित सामग्री वापस कर सकते हैं। ध्यान दें कि यह प्रदर्शन / भंडारण व्यापार बंद है। यदि आप अक्सर कंप्रेस किए गए डेटा को क्वेरी करते हैं, तो आपके पास धीमी कार्यक्षमता है क्योंकि टेक्स्ट को हर बार विघटित होना चाहिए।

नोट: JSON फ़ंक्शन केवल SQL Server 2016+ और Azure SQL डेटाबेस में उपलब्ध हैं।

अधिक इस लेख के स्रोत से पढ़ा जा सकता है

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/


एमएसडीएन से एएसपी.नेट और एंगुलरजेएस (लगभग 41 मिनट) के साथ एक सिंगल पेज एप्लीकेशन बनाना

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        // ... possible routing etc.

        // Setup to return json and camelcase it!
        var formatter = GlobalConfiguration.Configuration.Formatters.JsonFormatter;
        formatter.SerializerSettings.ContractResolver =
            new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver();
    }

यह चालू होना चाहिए, मैंने कोशिश की और यह काम किया।





sql json xml normalization structured-data