如何使用Postgres jsonb_path_query而不选择联盟

Question 1

db:Postgresql-14. 这将是一个罕见的转变，并且我在寻找建议的/改进，可以做所以我可以学习/磨练我的postgres/json技能(和速度/优化这一非常缓慢的查询)。

我们收到的可变尺寸/结构星从外部api。

每个json目的是调查的响应。每个嵌套的"问答"的对象可以有一个完全不同的结构。在总共大约有-5的已知结构。

响应的对象存储在一个jsonb列jsonb_ops杜松子酒的索引。

表具有约500,000行。每行的jsonb列的对象都具有约200套价值观。

我们的目标是提取所有的嵌套问答的答复到另一个表中的问题，答案。在目的地表，我们将做广泛的查询与FTS和卦，是瞄准为模式的简单性。这就是为什么我提取到一个简单的表格而不是做什么更异域与jsonb查询。还有一个很大的元数据令人讨厌在这些对象，我不需要。所以我也希望能够节省一些空间归档源表(这是5GB+索引).

具体地说，我爱学习一个更优雅的方式，穿越提取和计的目标表。

而且我一直无法找出一种方法投的结果，实际sql文本，而不是援引jsontext(通常我会用->>,::文字，或_text版本的jsonb功能)

这是一个非常简化的版本启目，以便于运行这一点。

谢谢你提前！

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

后续编辑后提炼和得到的结果，我需要

这是查询我结束了在运行。它采取了11min处理和插入34million记录。它是好的，因为它是一个时间的操作。

一些评论对我所做的更改

-我->和->>，而不是[n]因为我读，即使在pg14，下标不使用索引(未确定，如果该事项在从)
-对 "to_json(...) #>> '{}'" 是我转变id string到一个没有引用的字符串基于这样的：堆溢出答案

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;

最后编辑后接受下面的回答，因为解决方案

谢谢是@爱德华*H.回答，并更好地了解如何正确使用jsonb_path_query，我能够消除所有的 UNION SELECT，发现一些价值观已经失踪，并且除需要对该to_json丐。虽 CROSS JOIN LATERAL 是隐含的结果的方式的功能，它是最好的形式包括 JOIN 而不是逗号，因为它们更紧密结合，并更易于阅读。下面是最后的查询我的使用。

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

第一个想法 :remplace4的查询 UNION 1独特的查询。

第二个想法 ：发言 level1.value['answer'] as answer 在第一个查询听起来像声明 jsonb_path_query(level1.value, '$.answer')::jsonb as answer 在第二个查询。我认为，这两个查询返回同一组行，并将重复去的 UNION 两者之间的查询。

第三个观点 ：使用 jsonb_path_query 功能 FROM 条款，而不是的 SELECT 条款，使用 CROSS JOIN LATERAL 为了打破jsonb数据的步骤：

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)

-在那里survey_id=6633968和id=4

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

第一个想法 :remplace4的查询 UNION 1独特的查询。

第二个想法 ：发言 level1.value['answer'] as answer 在第一个查询听起来像声明 jsonb_path_query(level1.value, '$.answer')::jsonb as answer 在第二个查询。我认为，这两个查询返回同一组行，并将重复去的 UNION 两者之间的查询。

第三个观点 ：使用 jsonb_path_query 功能 FROM 条款，而不是的 SELECT 条款，使用 CROSS JOIN LATERAL 为了打破jsonb数据的步骤：

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)

-在那里survey_id=6633968和id=4

谢谢你的反馈意见。 -我可以告诉我需要的联盟，因为我迭代通过的所有价值的4种不同的结构星. -好的，我错过了，我已经以某种方式重复。 -json的功能包括在从隐含的"横向"，使它不必把它写出来(据我所知)-为#3,我不能，要工作。 [42883]错误：功能jsonb_path_query(记录，不明)不存在的暗示：不能匹配给定名称和参数类型。你可能需要添加明确的类型转换。
为#3我已经更新的查询，并希望这会工作的这个时候没有错误。关于联盟，我还是不明白为什么你需要它，你是什么意思"4种不同的结构星"? 他们是不同的列表相同，或者从不同的表吗？
我不得不做一些编辑你写的是什么，以使其工作，但最重要的是你带我来的路到一个更好的解决方案。你是正确的，我的缺乏了解jsonb_path_query意味着我补鞋工会在一起。来回答你的问题我需要价值从几个不同的钥匙要concat会在一起的一个柱。作为奖励，我发现了一些情况下值没有被抓获，在我原来的查询。我已经编辑的原始发布的最终解我的使用。再次感谢。

如何使用Postgres jsonb_path_query而不选择联盟

的问题

后续编辑后提炼和得到的结果，我需要

一些评论对我所做的更改

最后编辑后接受下面的回答，因为解决方案

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别