بعد
اجتماع لجنة 2019-03 ، حدث تجميد الميزات. لدينا عمود تقليدي تقريبًا: لقد
كتبنا بالفعل عن تجميد العام الماضي. الآن ، نتائج 2019: أي جديد سيتم إدراجها في PostgreSQL 12. في هذا الجزء من المراجعة المكرس لـ JSONPath ، يتم استخدام أمثلة وشظايا من تقرير "Postgres 12 in Etudes" ، الذي قرأه أوليغ بارتونوف في
Saint Highload ++ في سان بطرسبرغ في 9 أبريل من هذا العام ،.
JSONPath
كل ما يتعلق بـ JSON (B) مناسب ، في الطلب في العالم ، في روسيا ، وهذا واحد من أهم مجالات التطوير في Postgres Professional. ظهر نوع jsonb والوظائف والمشغلين للعمل مع JSON / JSONB في الإصدار 9.4 من PostgreSQL ، وقد صنعها فريق بقيادة Oleg Bartunov.
يوفر معيار SQL / 2016 للعمل مع JSON: JSONPath مذكورة هناك - مجموعة من أدوات معالجة البيانات داخل JSON ؛ JSONTABLE - وسيلة لتحويل JSON إلى الجداول العادية ؛ عائلة كبيرة من الوظائف والمشغلين. على الرغم من حقيقة أن JSON في Postgres قد تم دعمها لفترة طويلة ، في عام 2017 بدأ Oleg Bartunov وزملاؤه العمل على دعم المعيار. الامتثال للمعايير جيد دائمًا. من بين كل ما تم وصفه في المعيار ، واحد فقط ولكن التصحيح الأكثر أهمية هو JSONPath في الإصدار 12 ، لذلك سنتحدث عن ذلك في المقام الأول.
في العصور القديمة ، استخدم الناس JSON ، لتخزينه في حقول النص. في 9.3 ، ظهر نوع بيانات خاص لـ JSON ، لكن الوظيفة المرتبطة به لم تكن غنية ، وكانت طلبات هذا النوع تعمل ببطء بسبب الوقت الذي استغرقته في تحليل تمثيل نص JSON. هذا أوقف العديد من مستخدمي Postgres المحتملين الذين فضلوا قواعد بيانات NoSQL. زادت إنتاجية بوستجرس عند 9.4 ، وذلك بفضل O. Bartunov ، و A. Korotkov و F. Sigaev ، قدمت Postgres نسخة ثنائية من JSON - نوع jsonb.
ليس من الضروري تحليل jsonb في كل مرة ، لذا فإن العمل معها أسرع بكثير. من بين الوظائف الجديدة والمشغلين الذين نشأوا في وقت واحد ، يعمل البعض فقط مع نوع جديد ثنائي ، مثل المشغل المهم للوقوع
@> ، والذي يتحقق مما إذا كان هناك عنصر أو صفيف مدرج في JSONB معين:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
يعطي TRUE ، لأن الصفيف على الجانب الأيمن يدخل الصفيف على اليسار. لكن
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
سيعطي FALSE ، نظرًا لأن مستوى التداخل مختلف ، يجب تعيينه بشكل صريح. هل تم تقديم عامل التشغيل للنوع jsonb
؟ (علامة استفهام) تتحقق مما إذا كانت السلسلة هي مفتاح كائن أو عنصر صفيف في المستوى العلوي من قيم JSONB ، بالإضافة إلى عاملين آخرين مماثلين (التفاصيل
هنا ). يتم دعمها بواسطة فهارس GIN مع فئتين من مشغلي GIN. يتيح لك المشغل
-> (السهم) "التنقل" خلال JSONB ، ويعيد قيمة حسب المفتاح أو ، إذا كان صفيفًا ، حسب الفهرس. هناك
العديد من المشغلين للتحرك. ولكن لا توجد طريقة لتنظيم المرشحات التي تعمل بشكل مشابه لـ WHERE. لقد كان ذلك إنجازًا كبيرًا: بفضل jsonb ، بدأت Postgres في زيادة شعبيتها باعتبارها RDBMS مع ميزات NoSQL.
في عام 2014 ، طور A. Korotkov و O. Bartunov و F. Sigaev امتداد jsquery ، والذي تم تضمينه كنتيجة في Postgres Pro Standard 9.5 (وفي الإصدارات الأحدث من Standard و Enterprise). يوفر ميزات إضافية واسعة جدًا للعمل مع json (b). يحدد هذا الامتداد لغة الاستعلام لاستخراج البيانات من json (b) والفهارس لتسريع هذه الاستعلامات. كانت هذه الوظيفة مطلوبة من قِبل المستخدمين ، ولم يكونوا مستعدين لانتظار المعايير وإدراج ميزات جديدة في إصدار الفانيليا. تتجلى القيمة العملية أيضًا في حقيقة أن التطوير تم برعاية Wargaming.net. ملحق بتنفيذ نوع خاص - jsquery.
استعلام بهذه اللغة مضغوط ويبدو ، على سبيل المثال ، كما يلي:
SELECT '{"apt":[{"no": 1, "rooms":2}, {"no": 2, "rooms":3}, {"no": 3, "rooms":2}]}'::jsonb @@ 'apt.#.rooms=3'::jsquery;
نحن نسأل هنا ما إذا كان هناك "ثلاثة روبل" في مبنى سكني. يجب تحديد نوع jsquery لأن عامل التشغيلموجود الآن أيضًا في نوع jsonb. الوصف
هنا ، والعرض مع العديد من الأمثلة
هنا .
الإجمالي: كان لدى Postgres بالفعل كل شيء للعمل مع JSON ، ثم ظهر معيار SQL: 2016. اتضح أن دلالاتها لا تختلف كثيرا عن بلدنا في امتداد jsquery. من الممكن أن يلقي مؤلفو المعيار نظرة سريعة على jsquery ، واخترعوا JSONPath. كان على فريقنا أن ينفذ بطريقة مختلفة قليلاً ما لدينا بالفعل ، وبالطبع الكثير من الأشياء الجديدة أيضًا.
منذ أكثر من عام ، في مهرجان مارس ، تم تقديم ثمار جهودنا في البرمجة إلى المجتمع في شكل 3 تصحيحات كبيرة مع دعم لمعيار
SQL: 2016 :
SQL / JSON: JSONPath؛
SQL / JSON: وظائف ؛
SQL / JSON: JSON_TABLE.
ولكن تطوير التصحيح ليس هو العمل برمته ، ليس من السهل الترويج له ، خاصة إذا كانت البقع كبيرة وتؤثر على العديد من الوحدات. هناك حاجة إلى العديد من التكرار لمراجعة المراجعة ، ويجب ترقية التصحيح ، كما تفعل الشركات التجارية ، حيث تستثمر الكثير من الموارد (ساعات العمل). قام المهندس المعماري لـ Postgres Professional ، ألكساندر كوروتكوف ، بأخذها على عاتقه (نظرًا لأنه يتمتع الآن بوضع المرسل) وحقق اعتماد تصحيح JSONPath - الإصدار الرئيسي في هذه السلسلة من التصحيحات. الثاني والثالث الآن في حالة مراجعة الاحتياجات. يتيح لك JSONPath المركز التركيز العمل مع هيكل JSON (B) ومرن بدرجة كافية لإبراز شظاياه. من بين 15 نقطة منصوص عليها في المعيار ، يتم تنفيذ 14 نقطة ، وهذا أكثر مما هو الحال في Oracle و MySQL و MS SQL.
يختلف تدوين JSONPath عن عبارات Postgres للعمل مع تدوين JSON و JSQuery. يشار إلى التسلسل الهرمي بالنقاط:
$ .abc (في تدوين postgres 11 ، كان علي أن أكتب 'a ->' b '->' c ') ؛
$ - السياق الحالي للعنصر - في الواقع ، يُعرّف التعبير بـ $ منطقة json (b) المراد معالجتها ، بما في ذلك المنطقة الموجودة في المرشح ، والباقي من هذا غير متاح للعمل ؛
@ - السياق الحالي في تعبير المرشح - يتكرر عبر المسارات المتاحة في التعبير بـ $ ؛
[*] - مجموعة ؛
* - البدل ، في التعبير بـ $ أو @ يعني أي قيمة لجزء المسار ، ولكن مع مراعاة التسلسل الهرمي ؛
** - كجزء من التعبير بـ $ أو @ يمكن أن يعني أي قيمة لمقطع المسار دون مراعاة التسلسل الهرمي - من الملائم استخدامه إذا كنت لا تعرف مستوى تداخل العناصر ؛
المشغل "؟" يتيح لك تنظيم مرشح مشابه لما يلي:
$ .abc؟ (@ .x> 10) ؛
$ .abcxtype () ، بالإضافة إلى الحجم () ، double () ، ceiling () ، floor () ، abs () ، التاريخ () ، keyvalue () هي طرق.
قد يبدو استعلام باستخدام دالة jsonb_path_query (حول الوظائف أدناه) كما يلي:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > 3)'); jsonb_path_query_array
على الرغم من عدم تصحيح تصحيح خاص مع وظائف ، فإن تصحيح JSONPath يحتوي بالفعل على وظائف رئيسية للعمل مع JSON (B):
jsonb_path_exists('{"a": 1}', '$.a') true ( "?") jsonb_path_exists('{"a": 1}', '$.b') false jsonb_path_match('{"a": 1}', '$.a == 1') true ( "@>") jsonb_path_match('{"a": 1}', '$.a >= 2') false jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3, 4, 5 jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') 0 jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') [3, 4, 5] jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') [] jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3 jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') NULL
لاحظ أن المساواة في تعبيرات JSONPath هي مفردة "=" ، بينما في jsquery تكون مزدوجة: "==".
للحصول على رسوم توضيحية أكثر أناقة ، سنقوم بإنشاء JSONB في لوحة منزل ذات عمود واحد:
CREATE TABLE house(js jsonb); INSERT INTO house VALUES ('{ "address": { "city":"Moscow", "street": "Ulyanova, 7A" }, "lift": false, "floor": [ { "level": 1, "apt": [ {"no": 1, "area": 40, "rooms": 1}, {"no": 2, "area": 80, "rooms": 3}, {"no": 3, "area": 50, "rooms": 2} ] }, { "level": 2, "apt": [ {"no": 4, "area": 100, "rooms": 3}, {"no": 5, "area": 60, "rooms": 2} ] } ] }');
الشكل 1. الإسكان JSON شجرة مع الشقق ورقة المخصصة.هذا غريب JSON: له تسلسل هرمي مشوش ، لكنه مأخوذ من الحياة ، وفي الحياة غالبا ما يكون من الضروري العمل مع ما هو ، وليس مع ما ينبغي أن يكون. مسلحًا بإمكانيات الإصدار الجديد ، سنجد شققًا في الطابقين الأول والثاني ، ولكن ليست الأولى في قائمة شقق الطابق (على الشجرة التي تم تسليط الضوء عليها باللون الأخضر):
SELECT jsonb_path_query_array(js, '$.floor[0, 1].apt[1 to last]') FROM house;
في PostgreSQL 11 ، عليك أن تسأل هذا:
SELECT jsonb_agg(apt) FROM ( SELECT apt->generate_series(1, jsonb_array_length(apt) - 1) FROM ( SELECT js->'floor'->unnest(array[0, 1])->'apt' FROM house ) apts(apt) ) apts(apt);
الآن سؤال بسيط للغاية: هل هناك خطوط تحتوي (في أي مكان) على القيمة "موسكو"؟ حقا بسيط:
SELECT jsonb_path_exists(js, '$.** ? (@ == "Moscow")') FROM house;
في الإصدار 11 ، يجب عليك كتابة برنامج نصي ضخم:
WITH RECURSIVE t(value) AS ( SELECT * FROM house UNION ALL ( SELECT COALESCE(kv.value, e.value) AS value FROM t LEFT JOIN LATERAL jsonb_each ( CASE WHEN jsonb_typeof(t.value) = 'object' THEN t.value ELSE NULL END ) kv ON true LEFT JOIN LATERAL jsonb_array_elements ( CASE WHEN jsonb_typeof(t.value) = 'array' THEN t.value ELSE NULL END ) e ON true WHERE kv.value IS NOT NULL OR e.value IS NOT NULL ) ) SELECT EXISTS (SELECT 1 FROM t WHERE value = '"Moscow"');
التين. 2 شجرة الإسكان JSON ، تم العثور على موسكو!نحن نبحث عن أي شقة في أي طابق بمساحة 40 إلى 90 متر مربع:
select jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area > 40 && @.area < 90)') FROM house; jsonb_path_query ----------------------------------- {"no": 2, "area": 80, "rooms": 3} {"no": 3, "area": 50, "rooms": 2} {"no": 5, "area": 60, "rooms": 2} (3 rows)
نحن نبحث عن شقق تحتوي على غرف بعد الثالث ، وذلك باستخدام jason السكني لدينا:
SELECT jsonb_path_query(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query
وإليك طريقة عمل jsonb_path_query_first:
SELECT jsonb_path_query_first(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query_first
نرى أنه تم تحديد القيمة الأولى فقط التي تفي بحالة المرشح.
يسمى عامل التشغيل Boolean JSONPath لـ JSONBعامل التشغيل المطابق. فإنه يحسب المسند JSONPath عن طريق استدعاء وظيفة jsonb_path_match_opr.
عامل منطقي آخر هو @؟ - هذا اختبار للوجود ، يجيب على السؤال عما إذا كان تعبير JSONPath سيعيد كائنات SQL / JSON ، فإنه يستدعي الدالة jsonb_path_exists_opr:
'[1,2,3]' @@ '$[*] == 3' true; '[1,2,3]' @? '$[*] @? (@ == 3)' - true
يمكن تحقيق نفس النتيجة باستخدام عوامل تشغيل مختلفة:
js @? '$.a' js @@ 'exists($.a)' js @@ '$.a == 1' js @? '$ ? ($.a == 1)'
إن جمال مشغلي JSONPath Boolean هو دعمهم وتسريعهم من خلال مؤشرات GIN. jsonb_ops و jsonb_path_ops هما فئات المشغلين المقابلة. في المثال ، نقوم بتعطيل SEQSCAN ، نظرًا لأن لدينا microtable ، على الجداول الكبيرة المحسن نفسه سيختار فهرس الصورة النقطية:
SET ENABLE_SEQSCAN TO OFF; CREATE INDEX ON house USING gin (js); EXPLAIN (COSTS OFF) SELECT * FROM house WHERE js @? '$.floor[*].apt[*] ? (@.rooms == 3)'; QUERY PLAN
جميع وظائف النموذج jsonb_path_xxx () لها نفس التوقيع:
jsonb_path_xxx( js jsonb, jsp jsonpath, vars jsonb DEFAULT '{}', silent boolean DEFAULT false )
vars هو كائن JSONB لتمرير متغيرات JSONPath:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > $x)', vars => '{"x": 2}'); jsonb_path_query_array
من الصعب الاستغناء عن vars عندما ننضم إلى حقل يتضمن jsonb type في أحد الجداول. دعنا نقول أننا نقدم تطبيقًا يبحث عن شقق مناسبة للعاملين في هذا المنزل ذاته الذين قاموا بتدوين متطلباتهم بالنسبة إلى الحد الأدنى من المساحة في الاستبيان:
CREATE TABLE demands(name text, position text, demand int); INSERT INTO demands VALUES ('','', 85), ('',' ', 45); SELECT jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area >= $min)', vars => jsonb_build_object('min', demands.demand)) FROM house, demands WHERE name = ''; -[ RECORD 1 ]
يمكن لـ Lucky Pasha الاختيار من بين 4 شقق. لكن الأمر يستحق تغيير حرف واحد في الطلب - من "P" إلى "C" ، ولن يكون هناك خيار! فقط 1 شقة سوف تفعل.
تبقى كلمة رئيسية واحدة أخرى: الصمت عبارة عن علامة تمنع معالجة الأخطاء ؛ فهي في ضمير المبرمج.
SELECT jsonb_path_query('[]', 'strict $.a'); ERROR: SQL/JSON member not found DETAIL: jsonpath member accessor can only be applied to an object
الخطأ. ولكن هذا لن يكون خطأ:
SELECT jsonb_path_query('[]', 'strict $.a', silent => true); jsonb_path_query
بالمناسبة ، حول الأخطاء: وفقًا للمعايير الحسابية المعيارية في التعبيرات لا تعطي رسائل خطأ ، فهي في ضمير المبرمج:
SELECT jsonb_path_query('[1,0,2]', '$[*] ? (1/ @ >= 1)'); jsonb_path_query
عند حساب التعبير في المرشح ، يتم البحث في قيم الصفيف ، من بينها 0 ، لكن القسمة على 0 لا تنشئ خطأ.
ستعمل الوظائف بشكل مختلف اعتمادًا على الوضع المحدد: Strict أو Lax (في الترجمة "غير صارمة" أو "فضفاضة" ، يتم تحديدها افتراضيًا). لنفترض أننا نبحث عن مفتاح في وضع Lax في JSON ، حيث من الواضح أنه لا:
SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)'; ?column?
الآن في وضع صارم:
SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)'; ?column?
وهذا هو ، في ظل الوضع الليبرالي تلقينا FALSE ، مع صارمة حصلنا على NULL.
في وضع Lax ، يتم دائمًا توسيع مجموعة ذات تسلسل هرمي معقد [1،2 ، [3،4،5]] إلى [1،2،3،4،5]:
SELECT jsonb '[1,2,[3,4,5]]' @? 'lax $[*] ? (@ == 5)'; ?column?
في الوضع الصارم ، لن يتم العثور على الرقم "5" ، لأنه ليس في أسفل التسلسل الهرمي. للعثور عليه ، يجب عليك تعديل الاستعلام ، واستبدال "@" بـ "@ [*]":
SELECT jsonb '[1,2,[3,4,5]]' @? 'strict $[*] ? (@[*] == 5)'; ?column?
في PostgreSQL 12 ، JSONPath هو نوع البيانات. المعيار لا يقول أي شيء عن الحاجة لنوع جديد ، بل هو خاصية التنفيذ. مع النوع الجديد ، نحصل على عمل كامل مع jsonpath بمساعدة المشغلين والفهارس التي تسرع عملهم ، والتي توجد بالفعل لـ JSONB. خلاف ذلك ، يجب أن يكون JSONPath مدمجًا على مستوى رمز المُنفِّذ والمحسن.
يمكنك أن تقرأ عن بناء جملة SQL / JSON ، على سبيل المثال ،
هنا .
تدور مشاركة المدونة الخاصة بـ Oleg Bartunov حول
مطابقة معيار SQL / JSON لعام 2016 لكل من PostgreSQL و Oracle و SQL Server و MySQL.
فيما يلي
عرض تقديمي عن SQL / JSON.
وهنا
مقدمة ل SQL / JSON.