Jak korzystać z Postgres jsonb_path_query zamiast wyboru stowarzyszenia

Question 1

bazy danych:Postgresql-14. To będzie długimi okresami transformacja i szukam porady / ulepszenia, które można zrobić, żebym mógł studiować/doskonalić swoje umiejętności postgres/json (i przyspieszyć/zoptymalizować ten jest bardzo powolny zapytanie).

Otrzymujemy obiekty json zmiennego rozmiaru/struktury z zewnętrznego interfejsu api.

Każdy obiekt json jest odpowiedzią na ankietę. Każdy podrzędny obiekt "pytanie/odpowiedź" może mieć zupełnie inną strukturę. Wszystkim wiadomo około ~5 struktur.

Obiekty odpowiedzi przechowywane w kolumnie jsonb z indeksem jsonb_ops gin.

Tabela zawiera około 500 000 wierszy. Obiekt kolumny jsonb każdy wiersz zawiera około 200 zagnieżdżonych wartości.

Naszym celem jest,aby wyodrębnić wszystkie jego odpowiedzi na pytania/odpowiedzi do innej tabeli identyfikatorów,pytań i odpowiedzi. W tabeli przeznaczenia będziemy wykonywać rozległe kwerendy za pomocą FTS i gen. i dążymy do prostoty schematu. Dlatego czerpię dane w prosty stół zamiast robić coś bardziej egzotycznego z zapytaniami jsonb. W tych obiektach oraz wiele metadanych, które mi nie są potrzebne. Dlatego ja również mam nadzieję zaoszczędzić trochę miejsca, archiwizując oryginalną tabelę (to 5 GB + indeksy).

W szczególności, chciałbym poznać bardziej elegancki sposób przeszukiwania i pobierania json w tabeli docelowej.

I nie jestem w stanie znaleźć sposób, aby doprowadzić wyniki do faktycznego tekstu sql zamiast jsontext w cudzysłowie (normalnie bym użył ->>>, ::tekst lub _текстовую wersji funkcji jsonb)

Jest to bardzo uproszczona wersja obiektu json, aby ułatwić jego wykonanie.

Z góry dziękuję!

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

PÓŹNIEJSZA EDYCJA PO WYJAŚNIENIA I UZYSKANIA POŻĄDANEGO MI WYNIK

Jest to wniosek, który ja w końcu uruchomił. Na przetwarzanie i urzeczywistnienie 34 milionów rekordów zajęło 11 min. Że dobrze, tak jak jest to jednorazowa operacja.

Kilka uwag na temat wprowadzonych przeze mnie zmian

-Kiedyś -> i - > > > > > > zamiast [abonament], więc jak czytam, że nawet w pg14 abonament nie wykorzystuje indeksy (nie jestem pewien, czy to ma znaczenie w Z)
-w "to_json(...) #>> '{}'" oto jak mam przerobiony ciąg znaków json w wierszu bez cudzysłowu na podstawie tego: odpowiedź na przepełnienie stosu

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;

Ostateczna edycja po zaakceptowaniu przedstawionego poniżej odpowiedzi jako rozwiązanie

Dziękuję @Edouard H. odpowiedź i z lepszym zrozumieniem tego, jak prawidłowo używać jsonb_path_query, udało mi się wyeliminować wszystkie UNION SELECT, wykrywa brakujące wartości i napraw konieczność włamania to_json. Nawet pomimo faktu, że CROSS JOIN LATERAL pośrednio z funkcji json, jest to najlepsza forma, aby włączyć JOIN zamiast przecinków, ponieważ są one ściśle powiązane i łatwiej je czytać. Poniżej znajduje się ostatni wniosek, który używałem.

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

Pierwszy pomysł : przenieś 4 zapytania z UNION 1 unikalnej życzenie.

Drugi pomysł : zatwierdzenie level1.value['answer'] as answer w pierwszym zapytaniu brzmi jak stwierdzenie jsonb_path_query(level1.value, '$.answer')::jsonb as answer w drugim zapytaniu. Myślę, że oba zapytania zwracają ten sam zestaw wierszy, i duplikaty są usuwane UNION między dwoma zapytaniami.

Trzeci pomysł : użyj jsonb_path_query funkcja w FROM polecenie zamiast SELECT kolejno, za pomocą CROSS JOIN LATERAL aby złamać dane jsonb krok po kroku :

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)

- gdzie survey_id = 6633968 i id = 4

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

Pierwszy pomysł : przenieś 4 zapytania z UNION 1 unikalnej życzenie.

Drugi pomysł : zatwierdzenie level1.value['answer'] as answer w pierwszym zapytaniu brzmi jak stwierdzenie jsonb_path_query(level1.value, '$.answer')::jsonb as answer w drugim zapytaniu. Myślę, że oba zapytania zwracają ten sam zestaw wierszy, i duplikaty są usuwane UNION między dwoma zapytaniami.

Trzeci pomysł : użyj jsonb_path_query funkcja w FROM polecenie zamiast SELECT kolejno, za pomocą CROSS JOIN LATERAL aby złamać dane jsonb krok po kroku :

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)

- gdzie survey_id = 6633968 i id = 4

Dziękuję za opinię. - O ile mogę sądzić, muszę stowarzyszenie, bo przeżyłam wszystkie wartości 4 różnych zorganizowanego obiektów json. - Dobry połów, przegapiłem, że w jakiś sposób powielane to. - funkcje json, zawarte w FROM niejawnie "boczne", więc nie ma potrzeby ich przepisywania (AFAIK) - dla #3 nie jestem w stanie sprawić to działać. [42883] BŁĄD: funkcja jsonb_path_query(nagrywanie, nieznany) nie istnieje Wskazówka: Żadna funkcja nie jest zgodny z określonym imieniu i typów argumentów. Być może trzeba dodać wyraźne rzutowania typów.
Do #3 zaktualizowałem wniosek i mam nadzieję, że tym razem uda się bez błędów. Co do STOWARZYSZENIA, nadal nie rozumiem, po co to robisz i co masz na myśli pod "4 różnych zorganizowanych obiektów json" ? Czy są one różnymi kolumnami jednej i tej samej tabeli lub z różnych tabel ?
Musiałem dokonać kilku zmian w tym, co napisałeś, żeby to się udało, ale najważniejsze jest to, że poprowadził mnie drogą do znacznie lepszego rozwiązania. Masz rację, moje niezrozumienie jsonb_path_query oznaczało, że zbierałem związki razem. Aby odpowiedzieć na to pytanie, trzeba było połączyć wartości z kilku różnych kluczy w jedną kolumnę. Jako bonus znalazłem kilka przypadków, w których wartości nie zostały zarejestrowane w moim pierwotnym wniosku. Ja edytowany przez oryginalną publikację za pomocą ostatecznego rozwiązania, które używałem. Jeszcze raz dziękuję.

Jak korzystać z Postgres jsonb_path_query zamiast wyboru stowarzyszenia

Pytanie

PÓŹNIEJSZA EDYCJA PO WYJAŚNIENIA I UZYSKANIA POŻĄDANEGO MI WYNIK

Kilka uwag na temat wprowadzonych przeze mnie zmian

Ostateczna edycja po zaakceptowaniu przedstawionego poniżej odpowiedzi jako rozwiązanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii