Select union yerine Postgres jsonb_path_query nasıl kullanılır

Question 1

db: Postgresql-14. Bu seyrek bir dönüşüm olacak ve postgres/json becerilerimi öğrenebilmem/geliştirebilmem için yapılabilecek öneriler / geliştirmeler arıyorum (ve bu çok yavaş sorguyu hızlandırabilir/optimize edebilirim).

Harici bir apı'den değişken boyut / yapı json nesneleri alıyoruz.

Her json nesnesi bir anket yanıtıdır. Her iç içe geçmiş" soru/cevap " nesnesi oldukça farklı bir yapıya sahip olabilir. Toplamda yaklaşık ~ 5 bilinen yapı vardır.

Yanıt nesneleri, jsonb_ops gın dizini olan bir jsonb sütununda depolanır.

Tabloda yaklaşık 500.000 satır vardır. Her satırın jsonb sütun nesnesi yaklaşık 200 iç içe değere sahiptir.

Amacımız,iç içe geçmiş tüm soru/cevap yanıtlarını başka bir kimlik,soru, cevap tablosuna çıkarmaktır. Hedef tabloda FTS ve trigram ile kapsamlı sorgulama yapacağız ve şema basitliğini hedefliyoruz. Bu yüzden jsonb sorgulamasıyla daha egzotik bir şey yapmak yerine basit bir tabloya ayıklıyorum. Ayrıca sana ihtiyacım yok ben bu nesneler meta verileri işe yaramayan öğeleri bir çok şey var. Bu yüzden orijin tablosunu arşivleyerek de biraz yer kazanmayı umuyorum(5GB + dizinler).

Özellikle, json'u hedef tabloya geçirmenin ve çıkarmanın daha zarif bir yolunu öğrenmek isterim.

Ve sonuçları alıntı yapılan jsontext yerine gerçek sql metnine aktarmanın bir yolunu bulamadım (normalde ->>>, ::text veya jsonb işlevinin _text sürümünü kullanırdım)

Bu, yalnızca bunu çalıştırmayı kolaylaştırmak için json nesnesinin çok basitleştirilmiş bir sürümüdür.

Şimdiden teşekkür ederim.

create table test_survey_processing(
    id integer generated always as identity constraint test_survey_processing_pkey primary key,
    json_data jsonb
);
insert into test_survey_processing (json_data)
values ('{"survey_data": {"2": {"answer": "Option 1", "question": "radiobuttonquesiton"}, "3": {"options": {"10003": {"answer": "Option 1"}, "10004": {"answer": "Option 2"}}, "question": "checkboxquestion"}, "5": {"answer": "Column 2", "question": "Row 1"}, "6": {"answer": "Column 2", "question": "Row 2"}, "7": {"question": "checkboxGRIDquesiton", "subquestions": {"8": {"10007": {"answer": "Column 1", "question": "Row 1 : Column 1"}, "10008": {"answer": "Column 2", "question": "Row 1 : Column 2"}}, "9": {"10007": {"answer": "Column 1", "question": "Row 2 : Column 1"}, "10008": {"answer": "Column 2", "question": "Row 2 : Column 2"}}}}, "11": {"answer": "Option 1", "question": "Row 1"}, "12": {"answer": "Option 2", "question": "Row 2"}, "13": {"options": {"10011": {"answer": "Et molestias est opt", "option": "Option 1"}, "10012": {"answer": "Similique magnam min", "option": "Option 2"}}, "question": "textboxlist"}, "14": {"question": "textboxgridquesiton", "subquestions": {"15": {"10013": {"answer": "Qui error magna omni", "question": "Row 1 : Column 1"}, "10014": {"answer": "Est qui dolore dele", "question": "Row 1 : Column 2"}}, "16": {"10013": {"answer": "vident mol", "question": "Row 2 : Column 1"}, "10014": {"answer": "Consectetur dolor co", "question": "Row 2 : Column 2"}}}}, "17": {"question": "contactformquestion", "subquestions": {"18": {"answer": "Rafael", "question": "First Name"}, "19": {"answer": "Adams", "question": "Last Name"}}}, "33": {"question": "customgroupquestion", "subquestions": {"34": {"answer": "Sed magnam enim non", "question": "customgroupTEXTbox"}, "36": {"answer": "Option 2", "question": "customgroupradiobutton"}, "37": {"options": {"10021": {"answer": "Option 1", "option": "customgroupCHEC KBOX question : Option 1"}, "10022": {"answer": "Option 2", "option": "customgroupCHEC KBOX question : Option 2"}}, "question": "customgroupCHEC KBOX question"}}}, "38": {"question": "customTABLEquestion", "subquestions": {"10001": {"answer": "Option 1", "question": "customTABLEquestioncolumnRADIO"}, "10002": {"answer": "Option 2", "question": "customTABLEquestioncolumnRADIO"}, "10003": {"options": {"10029": {"answer": "OPTION1"}, "10030": {"answer": "OPTION2"}}, "question": "customTABLEquestioncolumnCHECKBOX"}, "10004": {"options": {"10029": {"answer": "OPTION1"}, "10030": {"answer": "OPTION2"}}, "question": "customTABLEquestioncolumnCHECKBOX"}, "10005": {"answer": "Aperiam itaque dolor", "question": "customTABLEquestioncolumnTEXTBOX"}, "10006": {"answer": "Hic qui numquam inci", "question": "customTABLEquestioncolumnTEXTBOX"}}}}}');
create index test_survey_processing_gin_index on test_survey_processing using gin (json_data);

-- the query I'm using (it works, but it is unmanageably slow)

-- EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON)
select level1.value['question'] question, level1.value['answer'] as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4
union
select level1.value['question'] question, jsonb_path_query(level1.value, '$.answer')::jsonb as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4
union
select level1.value['question'] question, jsonb_path_query(level1.value, '$.options.*.answer')::jsonb as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4
union
select level1.value['question'] question, jsonb_path_query(level1.value, '$.subquestions.*.*.answer')::jsonb as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4

RAFİNE ETTİKTEN VE İHTİYACIM OLAN SONUCU ALDIKTAN SONRA TAKİP DÜZENLEME

Sonunda çalıştırdığım sorgu bu. 34 Milyon kaydı işlemek ve eklemek 11 dakika sürdü. Bir kez operasyon olduğu gibi gayet iyi.

Yaptığım değişiklikler hakkında birkaç yorum

- Pg14'te bile bunu okuduğumdan beri [subscripting] yerine - > ve - > > > > > kullandım, subscripting'de dizinler kullanılmıyor (FROM içinde önemli olup olmadığından emin değilim)
-bu "to_json(...) #>> '{}'" buna dayanarak json dizesini tırnak içine alınmamış bir dizeye nasıl dönüştürdüm: yığın taşması cevabı

create table respondent_questions_answers as
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question, '' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.answer')) #>> '{}' as answer 
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1
union
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question,
       to_json(jsonb_path_query(level1.value, '$.options.*.option')) #>> '{}' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.options.*.answer')) #>> '{}' as answer
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1 
union
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.*.question')) #>> '{}' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.*.answer')) #>> '{}' as answer
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1
union
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.question')) #>> '{}' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.answer')) #>> '{}' as answer
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1;

Aşağıdaki cevabı çözüm olarak kabul ettikten sonra son düzenleme

@ Edouard H.'ye teşekkür ederim. cevap ve jsonb_path_query'yi doğru bir şekilde nasıl kullanacağımı daha iyi anlayarak, tüm bunları ortadan kaldırabildim. UNION SELECT, eksik olan bazı değerleri keşfedin ve to_json hack ihtiyacını ortadan kaldırın. Olsa bile CROSS JOIN LATERAL json işlevleriyle örtükse, dahil edilmesi daha iyi bir formdur JOIN virgül yerine, daha sıkı bağlandıkları ve okunması daha kolay oldukları için. Kullandığım son sorgu aşağıdadır.

SELECT concat_ws(' ',
    qu.value::jsonb->>'question'
,   an.answer::jsonb->>'question'
,   an.answer::jsonb->>'option') AS question
,   an.answer::jsonb->>'answer' AS answer
--      , tgsr.json_data->>'survey_data'
FROM test_survey_processing tgsr
         CROSS JOIN LATERAL jsonb_each(tgsr.json_data->'survey_data') AS qu
         CROSS JOIN LATERAL jsonb_path_query(qu.value::jsonb, '$.** ? (exists(@.answer))') AS an(answer)

Question 2

İlk fikir: 4 sorguyu şu şekilde yeniden yerleştirin: UNION 1 benzersiz sorgu ile.

İkinci fikir: ifade level1.value['answer'] as answer ilk sorguda ifade gibi geliyor jsonb_path_query(level1.value, '$.answer')::jsonb as answer ikinci sorguda. Her iki sorgunun da aynı satır kümesini döndürdüğünü ve çoğaltmaların şu şekilde kaldırıldığını düşünüyorum: UNION her iki sorgu arasında.

Üçüncü fikir: jsonb_path_query işlev içinde FROM madde yerine SELECT yan tümce, kullanma CROSS JOIN LATERAL jsonb verilerini adım adım parçalamak için :

SELECT qu.question->>'question' AS question
     , an.answer->>'answer' AS answer
     , tgsr.json_data->>'survey_data'
  FROM test_survey_processing tgsr
 CROSS JOIN LATERAL jsonb_each(tgsr.json_data->'survey_data') AS qu(question)
 CROSS JOIN LATERAL jsonb_path_query(qu.question, '$.** ? (exists(@.answer))') AS an(answer)

-- burada survey_ıd = 6633968 ve ıd = 4

Edouard · Answer 1 · 2021-11-24T19:50:54

İlk fikir: 4 sorguyu şu şekilde yeniden yerleştirin: UNION 1 benzersiz sorgu ile.

İkinci fikir: ifade level1.value['answer'] as answer ilk sorguda ifade gibi geliyor jsonb_path_query(level1.value, '$.answer')::jsonb as answer ikinci sorguda. Her iki sorgunun da aynı satır kümesini döndürdüğünü ve çoğaltmaların şu şekilde kaldırıldığını düşünüyorum: UNION her iki sorgu arasında.

Üçüncü fikir: jsonb_path_query işlev içinde FROM madde yerine SELECT yan tümce, kullanma CROSS JOIN LATERAL jsonb verilerini adım adım parçalamak için :

SELECT qu.question->>'question' AS question
     , an.answer->>'answer' AS answer
     , tgsr.json_data->>'survey_data'
  FROM test_survey_processing tgsr
 CROSS JOIN LATERAL jsonb_each(tgsr.json_data->'survey_data') AS qu(question)
 CROSS JOIN LATERAL jsonb_path_query(qu.question, '$.** ? (exists(@.answer))') AS an(answer)

-- burada survey_ıd = 6633968 ve ıd = 4

Teşekkürler geri bildirim için. - Anlayabildiğim kadarıyla, birliğe ihtiyacım var çünkü 4 farklı yapılandırılmış json nesnesinin tüm değerlerini yineliyorum. - İyi yakaladın, bir şekilde çoğalttığımı kaçırdım. - FROM içinde yer alan json işlevleri örtük olarak "yanal" dır, bu yüzden bunu yazmak gerekli değildir (AFAIK) - #3 için bunu çalıştıramadım. [42883] HATA: işlev jsonb_path_query (kayıt, bilinmiyor) yok İpucu: Hiçbir işlev verilen ad ve bağımsız değişken türleriyle eşleşmiyor. Açık tür dökümleri eklemeniz gerekebilir.
# 3 için sorguyu güncelledim ve bunun bu sefer hatasız çalışacağını umuyorum. SENDİKA ile ilgili olarak, neden buna ihtiyacınız olduğunu hala anlamıyorum ve "4 farklı yapılandırılmış json nesnesi" ile ne demek istiyorsunuz ? Aynı tablonun farklı sütunları mı yoksa farklı tablolardan mı ?
İşe yaraması için yazdıklarınla ilgili birkaç düzenleme yapmak zorunda kaldım ama en önemlisi beni çok daha iyi bir çözüme giden yolda yönlendirdin. Haklısın, jsonb_path_query hakkındaki anlayış eksikliğim sendikaları bir araya getirdiğim anlamına geliyordu. Sorunuzu cevaplamak için, bir sütuna birleştirilecek birkaç farklı anahtardan değerlere ihtiyacım vardı. Bonus olarak, orijinal sorgumda değerlerin yakalanmadığı birkaç durum buldum. Orijinal gönderiyi kullandığım son çözümle düzenledim. Tekrar teşekkürler.

Select union yerine Postgres jsonb_path_query nasıl kullanılır

Soru

RAFİNE ETTİKTEN VE İHTİYACIM OLAN SONUCU ALDIKTAN SONRA TAKİP DÜZENLEME

Yaptığım değişiklikler hakkında birkaç yorum

Aşağıdaki cevabı çözüm olarak kabul ettikten sonra son düzenleme

En iyi cevabı

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride