I have a question about formatting a column that contains a JSON string

[GiAnd75]

I would like to know if anyone has tried to format a column that contains a JSON string.

I stored in this JSON field some data about the review process of a file, and more precisely it contains an array of elements, one record for each version tracked that contains: version number, a list of feedback received by a group of users called "rev", a list of feedback received by a group of users called "app", each feedback is composed by username, result, timestamp and comments.
I would like to list all feedbacks received for each version by users with their comment and related timestamp, sorting versions DESC and feedback timestamp ASC.

Here the sample:

Content of the JSON field:
[{"app":[],"version":"0.2","rev":[{"name":"sp","result":"OK","timestamp":"2024-04-17T12:24:10Z","comments":" "},{"name":"lb","result":"KO","timestamp":"2024-04-18T10:48:51Z","comments":"vedi note"}]},
{"app":[],"version":"0.4","rev":[{"name":"sp","result":"OK","timestamp":"2024-04-18T13:46:08Z","comments":" "},{"name":"lb","result":"OK","timestamp":"2024-04-19T07:11:00Z","comments":" "}]}]

Output:
ver 0.4
rev
18/04/2024 13:46 sp OK " "
19/04/2024 07:11 lb OK " "
app

ver 0.2
rev
17/04/2024 12:24 sp OK " "
18/04/2024 10:48 lb KO "vedi note"
app

Thanks in advance for any help.

[tecchan1107]

Hi @GiAnd75 .

List Formatting does not provide an operator that would retrieve a value from json. Therefore, I think it is unfortunately not possible to create such an Output😢

[thechriskent]

@tecchan1107 is right that there isn't a way to pull JSON values from a list item using List Formatting. If you are storing the JSON as a string value in a text column, however, you could technically retrieve it, but you won't be able to do much more than display or inspect it.

The expression syntax is relatively limited and doesn't have the operators needed to parse structured data in the way you'd like for this. There are some things like forEach that can be combined with a split but that gets crazy complicated fast, and it doesn't work if your values contain spaces which yours almost certain would.

If you're looking to build something with a master/child setup where you can have multiple records for a single list item, this is what Lookup columns are for. Storing this information in a secondary list and then using a multi-lookup column would be the SharePoint way of doing this. I've got a video somewhere out there showing how to use a calculated column in this scenario to make a mini table of the secondary table within your primary list item - but there are several limitations on the column types available. You may have already considered and rejected that option for other reasons.

The other choice without going fully custom is Power Apps where you can parse the JSON into a collection and work with it as structured data.