Parse multiple Json lines in a cell

Ask for Help
1

Which formula or custom widget is appropriate? I want to parse Json line items into rows.

image
image1832Ă—559 99.5 KB

2

Hi @MK_2109! Just parsing JSON is easy using Python:

import json
json.loads($OrderLines)

i.e. literally type these two lines (starting with import json) into a formula in another column (e.g. named Parsed). The result will look the same as the OrderLines cell, but it will be parsed JSON. So other formulas could use e.g. $Parsed[0] to get the first order line.

The second part of your question is trickier. Do you want the parsing to automatically generate new rows? Those would be rows of another table. I can suggest a trick using summary tables.

  1. Extract an identifier for the new rows: create a new column orderLineID with the formula [x["orderLineID"] for x in $Parsed].
  2. Turn this new column orderLineID into a ChoiceList column. Ignore the fact that the choices have a red border: they are not recognized, but that doesn’t matter. (In fact, you can hide this column later.)
  3. Add a summary table, summarizing PO_V7 table by the new column orderLines. It will get a row for each orderLineID.
  4. In this summary table, you can extract the JSON object associated with the orderLineID as follows:
    next(line for line in $group.Parsed[0]
     if line["orderLineID"] == $orderLineID)
  5. If the column from step 4 is called orderLine, then it’s easy to add more columns to this summary table using e.g. $orderLine["quantity"], $orderLine["price"], etc. In your case, you might want float($orderLine["price"]) to turn it from a string to a number.

Note that as a summary table, the data here is purely derived from the original table. This creates some limitations (e.g. impossible to add non-formula columns). You could turn it into a real table by using the “Detach” option of summary tables. Then things like data columns and linking would work as for regular tables. But once detached, the data won’t auto-update – e.g. if you add a new order to the original table, its lines would not appear in the detached table.

Here, I made an example: https://public.getgrist.com/oryL2cZyN6KR/Parse-JSON-into-Rows.

I included both a summary table and a “Detached” table. In the latter, I tweaked formulas to leave only the most relevant columns.

1 Like