Table to json problem

[Data_consumer]

Hi!

I have a large JSON file that I would like to split by company to own JSON sets, and create a nested json so that the company name and date is not repeated within the JSON. Is this possible to do and how? Tried json path, converting to XML but just cant figure out how

“{
““Company name”” : “”! Company 1"”,
““Question”” : ““Revenue””,
““Answer”” : "“123"”,
““Year”” : ““2023-01-01"”
}”
“{
““Company name”” : “”! Company 1"”,
““Question”” : ““Personnel””,
““Answer”” : "“123"”,
““Year”” : ““2023-01-01"”
}”
“{
““Company name”” : “”! Company 2"”,
““Question”” : ““Revenue””,
““Answer”” : "“123"”,
““Year”” : ““2023-01-01"”
}”
“{
““Company name”” : “”! Company 2"”,
““Question”” : ““personnel””,
““Answer”” : "“123"”,
““Year”” : ““2023-01-01"”
}”

Thanks for the help, again!

[ArjenEX]

You’re really into JSON aren’t you

I’m afraid you need to go back and come up with a valid JSON (what you posted is not unfortunately) and also draft the expected output.

[Data_consumer]

Yes! But still practising Thanks, ill look up the real example file and come back

[Data_consumer]

Hi @ArjenEX Below is the table that I’m trying to convert into JSON. There can be multiple rows, and KPIs per company, and what I would like to do, is to build JSON data blocks from each company. A Quick explanation why I’m doing this and the goal:

We have built an own GPT model on top of Azure AI, that we are currently utilising for non-structured data. Now, I want to feed it structured data. As we have multiple tables of data with different topics, I would like to create company specific data blocks for our AI search index. As the data structure can either be transactional data or static data, I was thinking that converting the data into nested JSON “datablocks” would be the best possibility. so once this is completed, I need to figure out how to expand this JSON dataset with financial and other data from the company The best solution would of course be to have all the data from all of the companies in one JSON file /block to feed to the language model for analysis and comparison requests, but unfortunately GPT character amount limits still prevent me from doing this.

Company name	Question	Answer
Company 1	Revenue	3
Company 1	Total energy production	2
Company 2	Revenue	4
Company 2	Total energy production	3
Company 3	Revenue	4
Company 3	Total energy production	2

[ArjenEX]

@Data_consumer

Your explanation is still a bit confusing because the initial example you posted has the company in each object as well.

Which is easily achievable with a Table to JSON node. But since you mentioning trying a bunch of other methods I’m suspecting you’re looking for something more complex as desired output.

If company should be the root then you can go for something like this:

Example:
table to json.knwf (34.3 KB)

[Data_consumer]

Thanks @ArjenEX! The second example is exactly what I needed!