Manipulating JSON files

General Discussion
1

Are there tools that I'm missing out on for mass manipulation of JSON files?

As an example I just modified a bunch of tags to eliminate a Kepware server. So I needed to change the "opcItemPath" to point to the PLC address (e.g. "B3:1/11") and not the name (e.g. "Tag0") that was assigned in Kepware. The find/replace needs to be limited to the "opcItemPath" because the same string is in the "name" key and that should remain "Tag0"

This is how I did it:

  1. Export the ignition tags .JSON
  2. Create a simple text file (.csv) to map the replacements.
  3. Write a python script to:
    *Open and read the .JSON
    *Open the text file containing the name/address pairs and store in a pandas datatype
    *Iterate through the datatype using the replace() function to swap the address for name.
    *Write to a new .JSON
  4. Import the new .JSON into Ignition

It worked but I have the following issues:

  • The python script took a long time for me to write (due to my incompetence)
  • I prepended text to the tag name. This was to limit it to the "opcItemPath" but not the "name" key. Seems kludgy vs directly limiting my actions to a single key/value pair.

How would you accomplish this? My script took a long time to complete my script. Much longer than manually changing the tags although I knew that going into it and embraced it for experience to build competence in scripting. I'm much more of an Excel head and if this was a two-dimension table instead of a JSON I would have cranked out a hot mess of xlookup() and concatenate() functions and had a new file in less than five minutes.

How would complete the same task? Am I on the right path and just need to improve my python skill set? Are there better tools to work with .JSON on Windows OS? Bonus points for something with a GUI for idiots like me.

2

For top marks in terms of power/speed/flexibility (and abysmal marks in terms of 'usability'...), you can't beat jq.

It's a command line tool, but it can do basically any structured manipulation of JSON you can ask for.

The secret, in my opinion, seems to be to have a good idea of what you're asking for, and then feed it into ChatGPT/et al, instead of trying to remember jq's arcane syntax the two times a year you might need it.
Given your problem description, if you change your 'mappings' file to be JSON instead of CSV, you can apparently do something like this:

jq --slurpfile mappings mappings.json '
  .[] |= (.opcItemPath = ($mappings[0][.opcItemPath] // .opcItemPath))
' data.json
1 Like
3

Thank you. jq looks powerful.

I hate to admit it but sticking with Python might be my best route. From a personal development standpoint it may be better to build Python skills even if I'm doing things a bit dirty. I already lack depth in many things because I'm more of a jack of all trades type.

Python has a JSON module that I can look into as well. That likely has tools to limit the scope to key/value pairs. On my attempt earlier I just read the .json as a text file.

4

Agreed, +1 recommendation for jq.

Fantastically powerful, makes me feel like an idiot, and totally magical when you can get it do what you want (similar to regex).

2 Likes