sql - jsonb query with nested objects in an array -

मैं एक तालिका टीम वाले jsonb कॉलम नामित json । मैं एक ऐसी क्वेरी की तलाश कर रहा हूं जहां सभी टीमों को खिलाड़ियों के अपने सरणी में 3 , 4 और 7 मिलता है।

पहले पंक्ति:

  {"id":  
 तालिका में निम्न पंक्तियों में  json  डेटा शामिल है: 
 
 1, "नाम": "फूबार", "सदस्य": {"कोच": {"id": 1, "नाम": "एक दोस्त"}, "खिलाड़ी": [{"id": 2, "नाम "," डी दोस्त "}, {" id ": 3," नाम ":" सी दोस्त "}, {" id ": 4," नाम ":" डी दोस्त "}, {" id ": 6, दूसरी पंक्ति: 
 
 
 {"Id": "," "नाम": "बज़बार", "सदस्य": {"कोच": {"id": 11, "नाम": "एक दोस्त"}, "खिलाड़ी": [ आईडी ": 3," नाम ":" सी दोस्त " }, "नाम": "," "नाम": "ई दोस्त"}, {"id": 6, "नाम": "एफ दोस्त"}, {"id": 7, "नाम": "जी दोस्त "}", {"Id": 8, "नाम": "एच दोस्त"}]}}  
 
 टीमों की वांछित सूची प्राप्त करने के लिए क्वेरी को कैसा दिखता है? मैंने एक प्रश्न की कोशिश की है जहां मैं सदस्य खिलाड़ी  jsonb_array_elements (json - & gt; 'सदस्यों' - & gt; 'खिलाड़ियों') से एक सरणी बना सकता हूं - & gt; 'आईडी'  और उनकी तुलना करें, लेकिन सभी मैं पूरा कर पा रहा था, इसका नतीजा यह है कि किसी भी खिलाड़ी आईडी में से किसी एक टीम में उपलब्ध नहीं था, न कि सभी। 
 

आप एक बार में दो गैर-तुच्छ कार्यों का सामना कर रहे हैं। मुझे भ्रामक हूँ।

• एक जटिल नेस्टेड संरचना के साथ jsonb प्रक्रिया करें।
• दस्तावेज़ प्रकार पर एक रिलेशनल डिवीजन क्वेरी के बराबर चलाएं। < / Li>

सबसे पहले, jsonb_populate_recordset () के लिए एक पंक्ति प्रकार रजिस्टर करें। आप या तो बनायें से स्थायी रूप से एक प्रकार बना सकते हैं, या ऐड-हॉक उपयोग के लिए एक अस्थायी तालिका बना सकते हैं (सत्र के अंत में स्वचालित रूप से हटा दिए गए हैं):

  बनाएं Temp TABLE foo (id int); - केवल "आईडी", हमें "नाम" की आवश्यकता नहीं है  

हमें केवल id की आवश्यकता है, इसलिए इसमें नाम शामिल नहीं है < / code>।

JSON फ़ील्ड जो लक्ष्य पंक्ति प्रकार में प्रकट नहीं होती हैं, आउटपुट

क्वेरी

  SELECT T.json- & gt; 'आईडी' के रूप में टीम_आईडी, टीमों से p.players टी, बाद में (चयन करें * FROM jsonb_populate_recordset (null :: foo, t.json # & gt; '{सदस्य, खिलाड़ी}') )) एएस पी (खिलाड़ियों) WHERE p.players @ & gt; '{3,4,7}';  
  
  
  
  
  
  
  पी> खिलाड़ी के रिकॉर्ड के साथ JSON सरणी निष्कासित करता है:  
  t.json # & gt; '{सदस्य, खिलाड़ी}'  
  
 
 इनमें से, मैं सिर्फ  आईडी  के साथ पंक्तियों को अनदेखा कर रहा हूं: 
 
  jsonb_populate_recordset (null :: foo, t.json # & gt; '{सदस्यों, खिलाड़ी}' )  
 
 ... और उनको तुरंत एक पोस्टग्रेज़ सरणी में एकत्रित करें, इसलिए हम आधार तालिका में प्रति पंक्ति एक पंक्ति रखें: 
 
  SELECT ARRAY (। ..)  
 
 
 
 यह सब पार्श्व में शामिल होता है: 
 
 , बाद (SELECT ...) AS (खिलाड़ियों)  
 
 
 
 केवल उन लोगों को रखने के लिए परिणामस्वरूप सरणियों को तुरंत फ़िल्टर करें - साथ में: 
 
  WHERE P.players @ & gt; '{3,4,7}'  
 
  
 व्हाइला। 
 
 यदि आप बड़ी क्वेरी पर इस क्वेरी को बहुत कुछ चलाते हैं, तो आप एक नकली  IMMUTABLE  फ़ंक्शन बना सकता है जो कि ऊपर की तरह सरणी को निकालता है और इस सुपर फास्ट को बनाने के लिए इस फ़ंक्शन के आधार पर कार्यात्मक  जीआईएन सूचकांक  बना देता है। "फ़ैक्स" क्योंकि फ़ंक्शन अंतर्निहित पंक्ति प्रकार पर निर्भर करता है, अर्थात एक कैटलॉग लुकअप पर, और यदि वह परिवर्तन होता है तो बदल जाएगा। (इसलिए सुनिश्चित करें कि यह कोई परिवर्तन नहीं करता है।) यह एक जैसा है: 
 
 
 
 
 
 एक तरफ: 
 टाइप नामों जैसे  जेसन  कॉलम के नाम के रूप में (भले ही उसकी अनुमति हो), जो मुश्किल वाक्यविन्यास त्रुटियों और भ्रामक त्रुटि संदेश आमंत्रित करता है।