JSON Reader - doesn't read all the data.

[spiltan]

Hello,

I am using JSON Reader to read some data, however, the node only takes the first record of data. is there any feature in the node that I need to activate or change? Do I need to run the whole process via a loop? if yes, what loop I need to use? any examples that you can share, please?

[gonhaddock]

Hello @spiltan
The image that you posted, it’s showing a JSON’s preview view from ‘JSON Reader’ config.
Aiming to parse the JSON content, you will need to use some other nodes downstream. Typically these two:

JSON Path

Ungroup

For further support, you may have to provide some challenge details or share some sample data.

BR

[spiltan]

Here is what I used:

but I still see one row of data. Is there anything wrong with my logic? I tried to use loops, but it still gives me the same results. I used the loop after JSON reader node.

[gonhaddock]

Hello @spiltan
It’s hard to know from a capture.
I think is all about the query configuration within your ‘JSON Path’ nodes.

Can you check in your queries if the query type is single or collection?
You may have to config collection queries for all of your calls.

BR

[spiltan]

thank you so much for your response!
my JSON file shows 17k plus Ln when I am opening it in note, but it only reads the first Ln in JSON reader.

I have multiple “data”, each row of data has multiple “companyIds”, “Ids”, values and other fields.
I use JSON Path and Ungrouping nodes, but it only gives me the first ln of “data”. I am not sure how to tackle this. I tried to use loop but it did not work.

here is a sample data:

{“data”:{“code”:“Test 1”,“companyId”:“1f465168-3829-4a32-ba0a-f28a2ca39213”,“id”:“a626d73a-94fe-af41-9eb3-32d63187e740”,“isDeleted”:false,“labels”:[{“id”:“bac1d137-fd78-6746-971e-09447e1ed241”,“isLocalized”:false,“langCode”:“bg”,“lastModifiedDate”:“2023-09-19T20:10:00.947+00:00”,“value”:“Test 1”}],“lastModifiedDate”:“2023-09-19T20:10:00.943+00:00”,“level”:1,“lists”:[{“hasChildren”:false,“id”:“87a45548-546a-b74b-bdd6-c81bf4685489”,“lastModifiedDate”:“2023-09-19T20:10:00.963+00:00”}],“shortCode”:“Test 1”,“timestamp”:“2023-09-20T02:10:08.186Z”},“type”:“list-item.1970-01-01T00:00:00Z.upsert”,“time”:“2023-09-20T02:10:08.186Z”,“id”:“1f465168-3829-4a32-ba0a-f28a2ca39213.a626d73a-94fe-af41-9eb3-32d63187e740”}
{“data”:{“code”:“Implementation-C03-D05-CC02”,“companyId”:“1f465168-3829-4a32-ba0a-f28a2ca39213”,“id”:“26763a62-06ce-c440-9343-bc9a63544846”,“isDeleted”:false,“labels”:[{“id”:“8c6d3314-2ea4-5e4c-bbb4-155ec927c94e”,“isLocalized”:false,“langCode”:“bg”,“lastModifiedDate”:“2023-11-17T15:59:37.907+00:00”,“value”:“Cost Center 02”}],“lastModifiedDate”:“2023-11-17T15:59:37.903+00:00”,“level”:4,“lists”:[{“hasChildren”:true,“id”:“cb2b9137-9c42-0343-aeaa-3daf19301538”,“lastModifiedDate”:“2023-11-17T15:59:37.907+00:00”}],“parentId”:“e02e106d-e137-604f-aa4f-d35b54edfda2”,“shortCode”:“CC02”,“timestamp”:“2023-11-17T21:58:54.423Z”},“type”:“list-item.1970-01-01T00:00:00Z.upsert”,“time”:“2023-11-17T21:58:54.423Z”,“id”:“1f465168-3829-4a32-ba0a-f28a2ca39213.26763a62-06ce-c440-9343-bc9a63544846”}

[gonhaddock]

Hello @spiltan
This sample data is returning a reading error.

could you please test the sample data, and share it as validated.

Thx. in advance.

[spiltan]

Please accept my apologies for the delayed response!
I am using 2 JSON parse nodes and ungrouping, but I still only get one ln of data. Do you have an example of a workflow with JSON Reader, JSON Parse, and Loop that I can look into? I have not been able to find any yet.
As for the data, how can I check it? This is not the full set of data.

[gonhaddock]

Hello @spiltan
There are plenty of wf examples around, You can take a look into this one, worked out by myself (so I know better the content):

This one is from a JKI challenge as well. In this case we are parsing JSON obtained from REST services, [ Just KNIME It S01 _ CH12 _ Obtaining a List of Human Genes via REST ] . You might find it useful as well, that in this link you will find other colleagues solutions’ [ Justknimeit-12 – KNIME Community Hub ] , included KNIME official’s one .

Solutions to "Just KNIME It!" Challenge 12

This thread is for posting solutions to “Just KNIME It!” Challenge 12. Feel free to link your solution from KNIME Hub as well! Here is the challenge of the week: Just KNIME It! | KNIME Have an idea for a challenge?? We’d love to hear it! Feel free to write it here .

Let us know if we can provide further help with this subject.

BR

[spiltan]

Thank you so much for sharing these. I will get back to you and share if that works for my work or still have the same issues.

[spiltan]

I am still having the same issues. Here is another sample of my data (source: Schema - Expense Report (Concur Expense) | SAP Help Portal):

{“type”:“expense-reports.1970-01-01T00:00:00Z.upsert”,“id”:“bb817611-2a6e-4602-bffc-47cca48efd71.3C370CECEA594898BD17”,“time”:“2023-04-13T18:34:55.29Z”,“data”:{“approvalStatus”:“A_NOTF”,“auditStatus”:“NOTR”,“businessPurpose”:“”,“cashAdvanceReturnAmount”:0,“cashAdvanceUtilizedAmount”:0,“comments”:,“companyId”:“bb817611-2a6e-4602-bffc-47cca48efd71”,“country”:“US”,“creationTimestamp”:“2023-02-09T21:10:29.093Z”,“customFields”:[{“dataType”:“VARCHAR”,“id”:“OrgUnit1”,“label”:“Org Unit 1”,“value”:“HELLO”},{“dataType”:“VARCHAR”,“id”:“Custom4”,“label”:“Custom 04”,“value”:“testing”},{“dataType”:“VARCHAR”,“id”:“Custom5”,“label”:“Custom 05”,“value”:“testing”},{“dataType”:“VARCHAR”,“id”:“Custom6”,“label”:“Custom 06 - Set by Audit Rule”,“value”:“DEFAULT VALUE”},{“dataType”:“LIST”,“id”:“Custom16”,“label”:“Regional Area”,“value”:“6e252faa-5e0b-f845-8e63-07f637d0b259”}],“delegateApproved”:false,“delegateCreated”:false,“delegateSubmitted”:false,“everSentBack”:false,“exceptionApproved”:false,“exceptionLevelMax”:2147483647,“exceptionLevelTotal”:1,“exceptions”:[{“cleared”:false,“exceptionCode”:“BADHEADR”}],“expenses”:[{“adjustedAmount”:359.24,“allocations”:,“approvedAmount”:359.24,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:359.24,“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“17927d4d-afdd-b249-afb6-6b6abed035a0”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“parentId”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“paymentType”:“CASH”,“postedAmount”:359.24,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:335,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-01-01T00:00:00Z”,“transactionType”:“CHD”,“vendor”:{“description”:“AC Hotels”}},{“adjustedAmount”:359.24,“allocations”:,“approvedAmount”:359.24,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:359.24,“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“0727b43d-4c12-674b-b061-8c239615f1a0”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“parentId”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“paymentType”:“CASH”,“postedAmount”:359.24,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:335,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-01-02T00:00:00Z”,“transactionType”:“CHD”,“vendor”:{“description”:“AC Hotels”}},{“adjustedAmount”:359.24,“allocations”:,“approvedAmount”:359.24,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:359.24,“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“0592aab8-b728-8b4b-a1b7-d345a26f8853”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“parentId”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“paymentType”:“CASH”,“postedAmount”:359.24,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:335,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-01-03T00:00:00Z”,“transactionType”:“CHD”,“vendor”:{“description”:“AC Hotels”}},{“adjustedAmount”:359.24,“allocations”:,“approvedAmount”:359.24,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:359.24,“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“6d691a00-3f82-5343-bbbe-4343dc25534d”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“parentId”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“paymentType”:“CASH”,“postedAmount”:359.24,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:335,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-01-04T00:00:00Z”,“transactionType”:“CHD”,“vendor”:{“description”:“AC Hotels”}},{“adjustedAmount”:359.24,“allocations”:,“approvedAmount”:359.24,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:359.24,“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“a04b3794-9deb-6d43-a444-a24d397c58a9”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“parentId”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“paymentType”:“CASH”,“postedAmount”:359.24,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:335,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-01-05T00:00:00Z”,“transactionType”:“CHD”,“vendor”:{“code”:“1AC735FE5BE74CDC8D01361DBA61916B”,“description”:“AC Hotels”}},{“adjustedAmount”:12.8,“allocations”:,“approvedAmount”:12.8,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:12.8,“comments”:[{“addedBy”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“addedFor”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“commentText”:“3C370CECEA594898BD17”,“creationTimestamp”:“2023-03-02T18:04:33.4Z”}],“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.0669320686},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-03-01T00:00:00Z”,“hotelCheckoutDate”:“2023-03-03T00:00:00Z”,“id”:“3c6b99ba-2d35-064a-b026-7f901fe21bbd”,“imageRequired”:false,“isBillable”:false,“isPersonal”:false,“paymentType”:“CASH”,“postedAmount”:12.8,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:12,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-03-03T00:00:00Z”,“transactionType”:“REG”,“vendor”:{“code”:“3921E7437B56465783E6D413763B16C3”}},{“adjustedAmount”:0,“allocations”:,“approvedAmount”:0,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:0,“comments”:[{“addedBy”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“addedFor”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“commentText”:“3C370CECEA594898BD17”,“creationTimestamp”:“2023-02-09T21:13:52.373Z”}],“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:1,“exceptions”:[{“cleared”:false,“exceptionCode”:“VATRCPT”}],“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“paymentType”:“CASH”,“postedAmount”:2514.68,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:2345,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-01-01T00:00:00Z”,“transactionType”:“PAR”,“vendor”:{}},{“adjustedAmount”:128.03,“allocations”:,“approvedAmount”:128.03,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:128.03,“comments”:[{“addedBy”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“addedFor”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“commentText”:“3C370CECEA594898BD17”,“creationTimestamp”:“2023-03-02T18:07:28.173Z”}],“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.0669320686},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-03-01T00:00:00Z”,“hotelCheckoutDate”:“2023-03-03T00:00:00Z”,“id”:“9d63032b-c626-8c4f-b33b-c250384e047e”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“paymentType”:“CASH”,“postedAmount”:128.03,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:120,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-03-03T00:00:00Z”,“transactionType”:“REG”},{“adjustedAmount”:128.03,“allocations”:,“approvedAmount”:128.03,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:128.03,“comments”:[{“addedBy”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“addedFor”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“commentText”:“3C370CECEA594898BD17”,“creationTimestamp”:“2023-03-02T18:31:32.02Z”}],“country”:“DE”,“countrySubdivision”:“DE-HH”,“customFields”:[{“dataType”:“VARCHAR”,“id”:“Custom1”,“label”:“Custom 01”,“value”:“testing!”},{“dataType”:“VARCHAR”,“id”:“Custom4”,“label”:“Custom 04”,“value”:“testing”},{“dataType”:“LIST”,“id”:“Custom8”,“label”:“Regional Area”,“value”:“6e252faa-5e0b-f845-8e63-07f637d0b259”},{“dataType”:“VARCHAR”,“id”:“Custom20”,“label”:“Custom 20 - Copy from header Custom 06”,“value”:“DEFAULT VALUE”}],“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.0669320686},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-03-01T00:00:00Z”,“hotelCheckoutDate”:“2023-03-03T00:00:00Z”,“id”:“8bfee5ae-7cdf-a842-a533-b04ad58a4455”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“paymentType”:“CASH”,“postedAmount”:128.03,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:120,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-03-03T00:00:00Z”,“transactionType”:“REG”},{“adjustedAmount”:12,“allocations”:,“approvedAmount”:12,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:12,“comments”:[{“addedBy”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“addedFor”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“commentText”:“3C370CECEA594898BD17”,“creationTimestamp”:“2023-03-02T18:40:07.23Z”}],“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:1,“exceptions”:[{“cleared”:false,“exceptionCode”:“NOACCODE”}],“exchangeRate”:{“direction”:“M”,“value”:1},“expenseType”:“01045”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-03-01T00:00:00Z”,“hotelCheckoutDate”:“2023-03-03T00:00:00Z”,“id”:“c02a6e7f-8e85-d64b-b540-66720a534381”,“imageRequired”:false,“isBillable”:false,“isPersonal”:false,“paymentType”:“CASH”,“postedAmount”:12,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“AIRFR”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:12,“transactionCurrencyCode”:“USD”,“transactionDate”:“2023-03-03T00:00:00Z”,“transactionType”:“REG”},{“adjustedAmount”:0,“allocations”:,“approvedAmount”:0,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:0,“comments”:[{“addedBy”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“addedFor”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“commentText”:“3C370CECEA594898BD17”,“creationTimestamp”:“2023-03-02T18:45:40.147Z”}],“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:1,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.0669320686},“expenseType”:“01045”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-03-01T00:00:00Z”,“hotelCheckoutDate”:“2023-03-02T00:00:00Z”,“id”:“8d647a95-2ada-094b-b8d4-8594fe05bdb7”,“imageRequired”:false,“isBillable”:false,“isPersonal”:false,“paymentType”:“CASH”,“postedAmount”:24.54,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“AIRFR”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:23,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-03-02T00:00:00Z”,“transactionType”:“PAR”},{“adjustedAmount”:24.54,“allocations”:,“approvedAmount”:24.54,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:24.54,“country”:“DE”,“countrySubdivision”:“DE-HH”,“customFields”:[{“dataType”:“VARCHAR”,“id”:“OrgUnit1”,“label”:“Org Unit 1”,“value”:“org1 constant”},{“dataType”:“VARCHAR”,“id”:“OrgUnit2”,“label”:“Org Unit 2”,“value”:“Org”},{“dataType”:“VARCHAR”,“id”:“Custom1”,“label”:“Custom 01 - Conditional target”,“value”:“testing!”},{“dataType”:“VARCHAR”,“id”:“Custom4”,“label”:“Custom 04”,“value”:“testing”}],“exceptionCount”:1,“exceptions”:[{“cleared”:false,“exceptionCode”:“NOACCODE”}],“exchangeRate”:{“direction”:“M”,“value”:1.0669320686},“expenseType”:“01045”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-03-01T00:00:00Z”,“hotelCheckoutDate”:“2023-03-02T00:00:00Z”,“id”:“b4c980a1-e8a0-a64c-9aa8-abc412b6de41”,“imageRequired”:false,“isBillable”:false,“isPersonal”:false,“parentId”:“8d647a95-2ada-094b-b8d4-8594fe05bdb7”,“paymentType”:“CASH”,“postedAmount”:24.54,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“AIRFR”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:23,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2023-03-02T00:00:00Z”,“transactionType”:“CHD”},{“adjustedAmount”:359.24,“allocations”:,“approvedAmount”:359.24,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:359.24,“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“fad14f7b-9028-2d45-a2e7-706f0f83149d”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“parentId”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“paymentType”:“CASH”,“postedAmount”:359.24,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:335,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2022-12-30T00:00:00Z”,“transactionType”:“CHD”,“vendor”:{“description”:“AC Hotels”}},{“adjustedAmount”:359.24,“allocations”:,“approvedAmount”:359.24,“attendeeCount”:0,“businessPurpose”:“2 Different Tax Periods”,“calculatedAdjustedAmount”:0,“claimedAmount”:359.24,“country”:“DE”,“countrySubdivision”:“DE-HH”,“exceptionCount”:0,“exceptions”:,“exchangeRate”:{“direction”:“M”,“value”:1.07235734},“expenseType”:“LODNG”,“foreignOrDomestic”:“FRGN”,“hasEReceipt”:false,“hasMissingReceiptAffidavit”:false,“hasVAT”:false,“hotelCheckinDate”:“2023-01-01T00:00:00Z”,“hotelCheckoutDate”:“2023-01-06T00:00:00Z”,“id”:“458f044e-635b-5f42-8f3f-825ea7992cca”,“imageRequired”:true,“isBillable”:false,“isPersonal”:false,“parentId”:“df2b593c-689e-bd4c-bfee-192a3af41f7a”,“paymentType”:“CASH”,“postedAmount”:359.24,“receiptReceived”:false,“receiptRequired”:false,“receiptType”:“N”,“spendCategory”:“LODGA”,“taxes”:,“totalReclaimAdjustedAmount”:0,“totalReclaimPostedAmount”:0,“totalTaxAdjustedAmount”:0,“totalTaxPostedAmount”:0,“transactionAmount”:335,“transactionCurrencyCode”:“EUR”,“transactionDate”:“2022-12-31T00:00:00Z”,“transactionType”:“CHD”,“vendor”:{“description”:“AC Hotels”}}],“hasReceipts”:false,“hasReceivedCashAdvanceReturn”:false,“hasReceivedPaperReceipts”:false,“isFinancialIntegrationEnabled”:false,“isReceiptImageAvailable”:false,“isReceiptImageRequired”:true,“isReceiptRequired”:false,“isReopened”:false,“ledgerCode”:“DEFAULT”,“limitApproved”:false,“paymentStatus”:“P_NOTP”,“reimbursementCurrency”:“USD”,“rejectionReasonCodes”:,“reportDate”:“2023-02-09”,“reportId”:“3C370CECEA594898BD17”,“reportingGroup”:{“name”:“Global”},“reportName”:“2 Different Tax Periods”,“reportNumber”:“J0SW2H”,“reportOwner”:“9803bdd9-7202-4ef1-80ee-251aaf8f27f5”,“revisionTimestamp”:“2023-04-13T18:34:55.29Z”,“totalAmountDueCompanyCard”:0,“totalApprovedAmount”:2820.08,“totalClaimedAmount”:2820.08,“totalPaymentConfirmedAmount”:0,“totalPersonalAmount”:0,“totalPostedAmount”:2820.08,“workflowSteps”:}}

[spiltan]

by the way, I also used json schema validator node, and it gave me error.
ERROR JSON Schema Validator 5:1999:2031 Execute failed: (“ArrayIndexOutOfBoundsException”): null

[spiltan]

then again, it only shows me row 0. Am I using a wrong configuration?

[spiltan]

I think my issue is with the data formatting and the reader configuration window.
I used a smaller sample of data and include the data inside a bracket, and then separated each row with a coma. Then in the configuration window, I deselected the “Select with JSONPath”, and was able to get all the 15 rows data.
With my main dataset, when I deselect the “Select with JSONPath” option in the configuration window, it gives me the following error:

Execute failed: Expected end of input, but there were content: START_OBJECT

I just noticed that my files are JSONL format, not JSON. How can I read JSONL format in Knime?

[gonhaddock]

Hello @spiltan
I went back to your first JSON sample aiming to identify the error. And there’s a lack of format in your processing when generating the file.

JSON Reader - doesn't read all the data.

thank you so much for your response! my JSON file shows 17k plus Ln when I am opening it in note, but it only reads the first Ln in JSON reader. I have multiple “data”, each row of data has multiple “companyIds”, “Ids”, values and other fields. I use JSON Path and Ungrouping nodes, but it only gives me the first ln of “data”. I am not sure how to tackle this. I tried to use loop but it did not work. here is a sample data: {“data”:{“code”:“Test 1”,“companyId”:“1f465168-3829-4a32-ba0a-f28a2ca…

There are two things to take into account:

1. The first one is about quota encoding, however it can be from my side when copy paste from the chat. There are two type of quotation types “data” <> "data". Just check it. It’s a good use to select preformatted text from the upper ribbon ‘</>’ when pasting code in the chat.

2. The second one is that you are aggregating groups without separating them with a comma { ... }, { ... }, furthermore you need to add square brackets to wrap all together: [ { ... }, { ... } ]

Then, by replacing the right quotation and wrapping the curly brackets groups with square bracket symbols, plus separating them with a comma. You will get the expected interpretation for the ‘JSON Reader’ node.

BR

[spiltan]

Thank you so much for the explanation!
I found JSONL in KNIME that used Line Reader and used it on my end, now I am able to read all the rows.