You can translate the question and the replies:

Json array in data set - joining back to original data --- losing records when select * is used

I am seeing some odd behavior with a derived view that is the result of a field with JSON data being turned into an array and joined back to the original data set and the flattened. if I do a select * (no where clause) of the resulting join (after the array has been flattened) certain records are disappearing. for this example lets says ones with id = 1234. if I do a *select from the table-flat where id = 1234* I get a returned rows. I think it may have something to do with the fact that the JSON array in the field might be identical for different record IDs from the parent table (hopefully that makes sense). Is there way to include the original ID number when I am creating the JSON array view of that field to ensure that all of the unique record IDs are kept when the array is joined back the the parent view? I am at a loss.
JSON ARRAY missing records select query join flatten
user
10-08-2021 22:08:00 -0400
code

2 Answers

Hi, Generally, when joining views together on a condition, only the rows that would satisfy the condition would be displayed. In case the rows that satisfy the condition are not being displayed I would perform the following checks: * I would check if there are rows present in the source that would match the condition for join the 2 views * Also, if I wish to keep the specified column in the conversion of columns to Array, I would include the column in the NEST () when performing the conversion, such that the original columns remains even when the array is flattened. * I would also check the intermediate view that are used in creation of the derived view causing the issue for any conditions in place. * Finally, I would check the Execution Trace to compare the difference between how many rows are actually being got from the source and how many rows are finally being displayed. If you still need help and you are valid support user, then you can raise a new Support case at the [Denodo Support Site](https://support.denodo.com/) so that our Support Team will assist you. Hope this helps!
Denodo Team
11-08-2021 05:40:41 -0400
code
I figued out a solution. it was due to the fact that the array that was being created using the JSON source had rows where it was identical (no unique identifer. I added the id value to the array so when it was later joined back to the original table it would match successfully and not drop any rows. this replaces the first portion of the each JSON record to include the id using the column id (from the parent table). *replace(comments, '{"userTitle"', concat('{"id":"', id, '","userTitle"'))* Then I was able to join the JSON source back to the original table.
user
11-08-2021 06:58:14 -0400
You must sign in to add an answer. If you do not have an account, you can register here