![]() answer and with a better understanding of how to correctly use jsonb_path_query, I was able to eliminate all the UNION SELECT, discover some values that had been missing, and remove the need for the to_json hack. Values ('' as answerįrom test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1 įinal edit after accepting below answer as solution Insert into test_survey_processing (json_data) Id integer generated always as identity constraint test_survey_processing_pkey primary key, Thank you in advance! create table test_survey_processing( This is a very simplified version of the json object to ease just running this. ![]() Specifically I'd love to learn a more elegant way of traversing and extracting the json to the destination table.Īnd I've been unable to figure out a way to cast the results to actual sql text instead of quoted jsontext (normally I'd use ->, ::text, or the _text version of the jsonb function) So I'm also hoping to save some space by archiving the origin table (it's 5GB + indexes). There is also a lot of metadata cruft in those objects that I don't need. That is why I'm extracting to a simple table instead of doing anything more exotic with jsonb querying. On the destination table we'll be doing extensive querying with FTS and trigram, and are aiming for schema simplicity. Our goal is to extract all the nested question/answer responses into another table of id,question,answer. Each row's jsonb column object has about 200 nested values. Response objects are stored in a jsonb column that has a jsonb_ops gin index. In total there are about ~5 known structures. ![]() Each nested "question/answer" object can have a quite different structure. We receive variable size/structure json objects from an external api.Įach json object is a survey response. This will be an infrequent transformation, and I'm looking for recommendations / improvements that can be made so I may learn/hone my postgres/json skills (and speed/optimize this very slow query). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |