चल रहा है: PostgreSQL 9.6.2

मेरे पास एक तालिका में संग्रहीत डेटा है जो एक कुंजी/मूल्य जोड़ी के रूप में है। "कुंजी" वास्तव में एक जेसन ऑब्जेक्ट का पथ है, प्रत्येक एक संपत्ति है। तो उदाहरण के लिए यदि कुंजी "cogs", "props1", "value" थी, तो जेसन ऑब्जेक्ट ऐसा होगा:

{
  "cogs":{
     "props1": {
       "value": 100    
      }
  }
}

यदि संभव हो तो मैं किसी SQL क्वेरी के माध्यम से किसी भी तरह एक जेसन ऑब्जेक्ट का पुनर्निर्माण करना चाहता हूं। यहाँ परीक्षण डेटा सेट है:

drop table if exists test_table;
CREATE TABLE test_table
(
    id serial,
    file_id integer NOT NULL,
    key character varying[],
    value character varying,
    status character varying
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

insert into test_table (file_id, key, value, status)
values (1, '{"cogs","description"}', 'some awesome cog', 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","display"}', 'Giant Cog', null);
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props1","value"}', '100', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props1","id"}', 26, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props1","dimensions"}', '{"200", "300"}', null);
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props2","value"}', '200', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props2","id"}', 27, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"cogs","props2","dimensions"}', '{"700", "800"}', null);

insert into test_table (file_id, key, value, status)
values (1, '{"widgets","description"}', 'some awesome widget', 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","display"}', 'Giant Widget', null);
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props1","value"}', '100', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props1","id"}', 28, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props1","dimensions"}', '{"200", "300"}', null);
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props2","value"}', '200', 'not verified');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props2","id"}', 29, 'approved');
insert into test_table (file_id, key, value, status)
values (1, '{"widgets","props2","dimensions"}', '{"900", "1000"}', null);

मैं जिस आउटपुट की तलाश कर रहा हूं वह इस प्रारूप में है:

{
    "cogs": {
        "description": "some awesome cog",
        "display": "Giant Cog",
        "props1": {
            "value": 100,
            "id": 26,
            "dimensions": [200, 300]
        },
        "props2": {
            "value": 200,
            "id": 27,
            "dimensions": [700, 800]
        }
    },
    "widgets": {
        "description": "some awesome widget",
        "display": "Giant Widget",
        "props1": {
            "value": 100,
            "id": 28,
            "dimensions": [200, 300]
        },
        "props2": {
            "value": 200,
            "id": 29,
            "dimensions": [900, 1000]
        }
    }
}

कुछ समस्याएं जिनका मैं सामना कर रहा हूं:

  1. "मान" कॉलम में टेक्स्ट, नंबर और एक सरणी हो सकती है। किसी भी कारण से, knex.js का उपयोग करने वाला सर्वर-साइड कोड निम्नलिखित प्रारूप के रूप में पूर्णांकों की एक सरणी (यानी, [100,300]) को पोस्टग्रेज में संग्रहीत कर रहा है: {"100", "300"}। मुझे यह सुनिश्चित करने की ज़रूरत है कि मैं इसे पूर्णांक की सरणी के रूप में भी निकालूं।

  2. इसे यथासंभव गतिशील बनाने का प्रयास किया जा रहा है। हार्ड-कोडिंग सरणी लुकअप मानों के बजाय "कुंजी" पथ की गहराई मौजूद है .... यह पता लगाने के लिए शायद एक पुनरावर्ती प्रक्रिया।

  3. json_object_agg गुणों को एक ही वस्तु में समूहित करने के लिए अच्छी तरह से काम करता है। हालांकि यह एक शून्य मान मारते समय टूट जाता है। तो अगर "कुंजी" कॉलम में केवल दो मान हैं (यानी, "कॉग", "विवरण"), और मैं लंबाई तीन (यानी, "कॉग", "प्रोप्स 1", "वैल्यू") की एक सरणी को एकत्रित करने का प्रयास करता हूं, यह तब तक टूटेगा जब तक कि मैं केवल लंबाई 3 के सरणियों पर फ़िल्टर नहीं करता।

  4. इनपुट के क्रम को सुरक्षित रखें। नीचे @klin समाधान अद्भुत है और मुझे वहां से 95% मिलता है। हालाँकि मैं आदेश को संरक्षित करने का उल्लेख करने में विफल रहा ...

3
dvsoukup 23 मई 2017, 20:33
कृपया - संस्करण
 – 
Vao Tsun
23 मई 2017, 21:08

1 उत्तर

सबसे बढ़िया उत्तर

एक गतिशील समाधान के लिए कुछ काम की जरूरत होती है।

सबसे पहले, हमें टेक्स्ट ऐरे और मान को jsonb ऑब्जेक्ट में बदलने के लिए एक फ़ंक्शन की आवश्यकता होती है।

create or replace function keys_to_object(keys text[], val text)
returns jsonb language plpgsql as $$
declare
    i int;
    rslt jsonb = to_jsonb(val);
begin
    for i in select generate_subscripts(keys, 1, true) loop
        rslt := jsonb_build_object(keys[i], rslt);
    end loop;
    return rslt;
end $$;

select keys_to_object(array['key', 'subkey', 'subsub'], 'value');

              keys_to_object              
------------------------------------------
 {"key": {"subkey": {"subsub": "value"}}}
(1 row)

इसके बाद, jsonb ऑब्जेक्ट को मर्ज करने के लिए एक अन्य फ़ंक्शन (देखें JSONB मानों को PostgreSQL में मर्ज करना)।

create or replace function jsonb_merge(a jsonb, b jsonb) 
returns jsonb language sql as $$ 
select 
    jsonb_object_agg(
        coalesce(ka, kb), 
        case 
            when va isnull then vb 
            when vb isnull then va 
            when jsonb_typeof(va) <> 'object' or jsonb_typeof(vb) <> 'object' then vb 
            else jsonb_merge(va, vb) end 
        ) 
    from jsonb_each(a) e1(ka, va) 
    full join jsonb_each(b) e2(kb, vb) on ka = kb 
$$;

select jsonb_merge('{"key": {"subkey1": "value1"}}', '{"key": {"subkey2": "value2"}}');

                     jsonb_merge                     
-----------------------------------------------------
 {"key": {"subkey1": "value1", "subkey2": "value2"}}
(1 row) 

अंत में, उपरोक्त फ़ंक्शन के आधार पर एक समुच्चय बनाते हैं,

create aggregate jsonb_merge_agg(jsonb)
(
    sfunc = jsonb_merge,
    stype = jsonb
);

और हम कर रहे हैं:

select jsonb_pretty(jsonb_merge_agg(keys_to_object(key, translate(value, '{}"', '[]'))))
from test_table;

                 jsonb_pretty                 
----------------------------------------------
 {                                           +
     "cogs": {                               +
         "props1": {                         +
             "id": "26",                     +
             "value": "100",                 +
             "dimensions": "[200, 300]"      +
         },                                  +
         "props2": {                         +
             "id": "27",                     +
             "value": "200",                 +
             "dimensions": "[700, 800]"      +
         },                                  +
         "display": "Giant Cog",             +
         "description": "some awesome cog"   +
     },                                      +
     "widgets": {                            +
         "props1": {                         +
             "id": "28",                     +
             "value": "100",                 +
             "dimensions": "[200, 300]"      +
         },                                  +
         "props2": {                         +
             "id": "29",                     +
             "value": "200",                 +
             "dimensions": "[900, 1000]"     +
         },                                  +
         "display": "Giant Widget",          +
         "description": "some awesome widget"+
     }                                       +
 }
(1 row)
3
klin 23 मई 2017, 22:43
मैं आज शाम को तुम्हारे नाम से एक ठंडी पी रहा हूँ! आश्चर्यजनक है कि यह काम करता है! स्पष्ट रूप से मुझे बहुत कुछ सीखना है :)
 – 
dvsoukup
23 मई 2017, 22:36
क्या एकत्रीकरण के दौरान पंक्ति क्रम को संरक्षित करना संभव है? मैं कस्टम समुच्चय पर पढ़ रहा हूं और अभी भी इससे थोड़ा भ्रमित हूं, इस प्रकार मेरा उत्तर यहां है।
 – 
dvsoukup
23 मई 2017, 23:37
दुर्भाग्य से नहीं। JSON परिभाषा के अनुसार नाम/मूल्य जोड़े का संग्रह अनियंत्रित है। मूल्यों की आदेशित सूची को संग्रहीत करने के लिए आपको JSON सरणी का उपयोग करना चाहिए। json.org देखें। पोस्टग्रेज JSONB किसी json संग्रह में किसी ऑर्डर की गारंटी नहीं देता है।
 – 
klin
23 मई 2017, 23:54
हालाँकि, आप jsonb के बजाय json का उपयोग करने का प्रयास कर सकते हैं। पूरी स्क्रिप्ट में jsonb को json से बदलें और jsonb_pretty() को हटा दें (इस फ़ंक्शन में कोई json समकक्ष नहीं है)। वस्तुओं का क्रम तालिका में जैसा ही रहना चाहिए, हालांकि यह जोंस प्रकार की अनिर्दिष्ट विशेषता है।
 – 
klin
24 मई 2017, 01:23
ये परिवर्तन किए हैं और पुष्टि कर सकते हैं कि यह निश्चित रूप से संरक्षित आदेश है! कितना महान हैं। इसके बारे में बहुत कुछ पढ़ें और ऐसा लगता है कि JSON प्रकार JSONB से भी बदतर प्रदर्शन करता है ... हालांकि मेरे मामले में यह ध्यान देने योग्य नहीं है। आपके सहयोग के लिए धन्यवाद!
 – 
dvsoukup
25 मई 2017, 18:06