parse JSON list of n values

Question

Hi,&nbsp;I have the following field which is in the following format:&nbsp;- This field contains ONE or a LIST of patterns inside the brackets as per below:&nbsp;[&nbsp;{"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"}]&nbsp;or[{"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"}, {"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"}, {"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"}, {"qty":"1","len":"1.32","wid":"1.08","hyt":"1.4"}, ....]&nbsp;&nbsp;Is there any way I can loop into this field and get all values for qty, len, wid, and hyt keys?&nbsp;I need to perform a calculation using all these fields.&nbsp;Thanks&nbsp;

ppm1 · Accepted Answer

You can just use the Json.Document function to extract the list of records, convert that to a table, and then expand the fields. See example below. Just create a new query, open advanced editor, and replace the code there with this.let
    Source = "[

{""qty"":""1"",""len"":""1.32"",""wid"":""1.08"",""hyt"":""1.4""},

{""qty"":""1"",""len"":""1.32"",""wid"":""1.08"",""hyt"":""1.4""}]",
    Custom1 = Json.Document(Source),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"qty", "len", "wid", "hyt"}, {"qty", "len", "wid", "hyt"})
in
    #"Expanded Column1"Pat&nbsp;

Forum Discussion

parse JSON list of n values

1 Reply

Related content

JSON Parsing - Each Value as T/F Column

Parsing JSON Using a Schema

JSON Data parsing issue

Parsing Complex Json

JSON parsing failure - name used more than once