Json Array Split

Question

Hi everyone,

I have a JSON value like this;

{"amount":{"due":0,"expected":0,"paid":0},"tax":{"due":0,"expected":0,"paid":0}}

I need to split this JSON like amount column and tax column. And this data is providing in SQL table. After reading this table, in derived column I'm trying to split that JSON with this code :

split(replace(replace(replace(fee, '[', ''),']',''),'},','},'),'%%},')

After that splitting I'm getting two column; amount and tax. Although there is a value in amount object, it does not appear in the column. The output like this;

So, can you suggest any other solution for splitting or should I change the code that I'm trying to split

Answer 1

Hello @Deniz Alkan ,
Thanks for the question and using MS Q&A platform.

Hello @Deniz Alkan and welcome to Microsoft Q&A.

Given how the data is already in JSON format, I think there must be an easier way than doing string operations. Depending on exactly how you want the data output, there are several options.

Upon further testing, I think that blank amount column is a bug. Anyway, here is an easier way:

Use a parse activity to turn that "json-as-text" into a real json, a complex type.
Use derived column to pick the parts you want, whether putting {due , expected, paid} all into one column or, giving each of them their own column.

I think you hit a bug in the graphical interface, not the flow itself because the displated output of mine is blank too...

However the data is available in the next activity!

Although the display seems all off-kilter. Actually, the more I look at it, the more seems wrong... It is displaying individual columns when that is not what is asked for.

Compare to inspect.

Okay, time to write to output to settle what exactly is in play here...

Please do let me if you have any queries.

Thanks
Martin

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hello @MartinJaffer-MSFT

First of all, thanks a lot for this detailed answer.

I already chosed Json settings as single document.

After Parse node, I added a SELECT node and now I can read the data. Like you said it was a bug.