JSON - Multiple Records to Variables

Questions & Suggestions
,
1

My goal: take multiple JSON records, parse them into separate variables and send an email containing the variables. I've looked at using JQ but it's beyond me at the moment so I'm reaching out for assistance here.

My JSON looks like this:

{
  "records": [
    {
      "id": "rec9L7D09MFoYd47z",
      "fields": {
        "Student First Name": "Bart",
        "Student Last Name": "Simpson",
        "Parent First Name": "Homer",
        "Parent Last Name": "Simpson",
        "Email": "doh@thesimpsons.net",
        "Phone": "1234567890",
        "Instrument": [
          "piano"
        ],
        "Teacher": [
          "Edna Krabapple"
        ],
        "Type": "child",
        "Days Since First Lesson": "6"
      },
      "createdTime": "2021-07-12T22:38:49.000Z"
    },
    {
      "id": "rechGNEs6DnLKDBDf",
      "fields": {
        "Student First Name": "Lisa",
        "Student Last Name": "Simpson",
        "Parent First Name": "Homer",
        "Parent Last Name": "Simpson",
        "Email": "doh@thesimpsons.net",
        "Phone": "1234567890",
        "Instrument": [
          "piano"
        ],
        "Teacher": [
          "Mr. Hoover"
        ],
        "Type": "child",
        "Days Since First Lesson": "6"
      },
      "createdTime": "2021-07-12T22:40:42.000Z"
    },
    {
      "id": "recbz6Y6AEUVxQQGj",
      "fields": {
        "Student First Name": "Bobby",
        "Student Last Name": "Hill",
        "Parent First Name": "Hank",
        "Parent Last Name": "Hill",
        "Email": "propane@propaneaccessories.net",
        "Phone": "8872394789",
        "Instrument": [
          "piano"
        ],
        "Teacher": [
          "Cotton Hill"
        ],
        "Type": "child",
        "Days Since First Lesson": "6"
      },
      "createdTime": "2021-07-12T23:33:10.000Z"
    },

  ]
}

I'd like my emails to look like this:

Student: Bart Simpson
Lesson: piano with Edna Krabapple

Parent: Homer Simpson
Phone: 1234567890
Email: doh@thesimpsons.net

So... how do I get the items from the JSON items into variables (and then compose the email - I know how to AppleScript this already) and have KM go over all of the available records until finished?

2

JSON is, of course, simply JavaScript object notation, and a detour through AppleScript would make all this rather harder and more complicated.

You may, however, be able to decipher and tweak the JS in this version for a Keyboard Maestro Execute a JavaScript for Automation action:

JSON parsed for email.kmmacros (4.2 KB)

Keyboard Maestro Export.png1356×2414 354 KB

Expand disclosure triangle to view JS Source 
(() => {
    "use strict";

    const main = () => {
        const
            kme = Application("Keyboard Maestro Engine"),
            records = JSON.parse(
                kme.getvariable("jsonString")
            ).records,

            // JavaScript list indexes start at zero.
            nth = 0,
            record = records[nth],
            fields = record.fields;

        // First record
        return `
Student: ${fields["Student First Name"]} ${fields["Student Last Name"]}
Lesson: ${toSentence(fields.Instrument[0])} with ${fields.Teacher[0]}

Parent: ${fields["Parent First Name"]} ${fields["Parent Last Name"]}
Phone: ${fields.Phone}
Email: ${fields.Email}
`;
    };

    // --------------------- GENERIC ---------------------

    // toSentence :: String -> String
    const toSentence = s =>
        // Sentence case - initial char capitalized
        // and rest lowercase.
        (0 < s.length) ? (
            s[0].toUpperCase() + s.slice(1)
            .toLowerCase()
        ) : s;

    return main();
})();

A few things to notice:

  1. Your JSON sample there needs a small fix to make it parseable – just prune out the trailing comma after the last of your records
  2. JS list indexes start at 0
  3. When the field name includes spaces, you need to use the ${fields["Student First Name"]} notation, but when there are no spaces you can abbreviate to the dot notation: Phone: ${fields.Phone}
3

Thank you so much, the output looks great.

Any idea how I can have KM go over ALL of the records in the JSON (Bart Simpson, Lisa Simpson, Bobby Hill) and save the individual outputs to a series of variables (i.e. Student1, Student2, Student3, etc.)?

4

Stepping back a moment from "a series of variables", could you give us a broader picture of the output, and how you would use those variables ?

Are you generating one email for each record (a series of emails) or just one email which aggregates it all ?

5

One email that aggregates it all would be best.

6

With each of these records separated, for example, by one or two blank lines ?

7

Any sort of separator would be most welcome, just to make things more readable.

Maybe a blank line followed by a line of asterisks or dashes and one more blank line?

8

This variant:

  1. Aggregates
  2. Shows an explanatory alert if there are glitches in the incoming JSON

(it's fractionally more elaborate, in order to get explanatory messages if the JSON needs a fix – let me know if any opacities are making it harder to use)

JSON parsed for email (with JSON glitch reporting).kmmacros (5.9 KB)

Keyboard Maestro Export.png1356×2414 367 KB

Expand disclosure triangle to view JS Source 
(() => {
    "use strict";

    // Rob Trew @2021
    // Draft 0.002

    const main = () => {
        const kme = Application("Keyboard Maestro Engine");

        return either(
            msg => alert("JSON Syntax")(msg)
        )(
            jsonData => jsonData.records.map(
                record => {
                    const fields = record.fields;

                    // First record
                    return `
Student: ${fields["Student First Name"]} ${fields["Student Last Name"]}
Lesson: ${toSentence(fields.Instrument[0])} with ${fields.Teacher[0]}

Parent: ${fields["Parent First Name"]} ${fields["Parent Last Name"]}
Phone: ${fields.Phone}
Email: ${fields.Email}
`;
                }
            ).join("\n***\n")
        )(
            jsonParseLR(
                kme.getvariable("jsonString")
            )
        );
    };

    // ----------------------- JXA -----------------------

    // alert :: String => String -> IO String
    const alert = title =>
        s => {
            const sa = Object.assign(
                Application("System Events"), {
                    includeStandardAdditions: true
                });

            return (
                sa.activate(),
                sa.displayDialog(s, {
                    withTitle: title,
                    buttons: ["OK"],
                    defaultButton: "OK"
                }),
                s
            );
        };

    // --------------------- GENERIC ---------------------

    // Left :: a -> Either a b
    const Left = x => ({
        type: "Either",
        Left: x
    });


    // Right :: b -> Either a b
    const Right = x => ({
        type: "Either",
        Right: x
    });


    // either :: (a -> c) -> (b -> c) -> Either a b -> c
    const either = fl =>
        // Application of the function fl to the
        // contents of any Left value in e, or
        // the application of fr to its Right value.
        fr => e => e.Left ? (
            fl(e.Left)
        ) : fr(e.Right);


    // jsonParseLR :: String -> Either String a
    const jsonParseLR = s => {
        try {
            return Right(JSON.parse(s));
        } catch (e) {
            return Left(
                [
                    e.message,
                    `(line:${e.line} col:${e.column})`
                ].join("\n")
            );
        }
    };


    // toSentence :: String -> String
    const toSentence = s =>
        // Sentence case - initial char capitalized
        // and rest lowercase.
        (0 < s.length) ? (
            s[0].toUpperCase() + s.slice(1)
            .toLowerCase()
        ) : s;

    return main();
})();
2 Likes
9

Thank you once again - this is exactly what I was hoping to achieve, and would never have been able to do it myself!

1 Like
10

PS I notice that the Instrument and Teacher values are lists,

might there be any cases of multiple instruments or multiple teachers in those lists ?

If there are, and you can show sample inputs and outputs for that kind of case, we can adjust the code a little to allow for them.

11

Haha, thankfully not! Single selections only.

1 Like