JSON filtering + reporting question

WEB TMS WEB Core
1

I have a question about filtering and formatting JSON data received from an API, and I'm curious how others might solve it. It's pretty simple, but I wanted to include data here so you can play with it if desired.

Here's a JSON response I got from WeatherAPI.com and I didn't find a way of filtering for including specific fields:
—————————————————————
{"body":{"current":{"cloud":75,"condition":{"code":1003,"icon":"//cdn.weatherapi.com/weather/64x64/night/116.png","text":"Partly cloudy"},"dewpoint_c":6.1,"dewpoint_f":42.9,"feelslike_c":18.3,"feelslike_f":64.9,"gust_kph":6.6,"gust_mph":4.1,"heatindex_c":19.4,"heatindex_f":66.9,"humidity":37,"is_day":0,"last_updated":"2025-02-08 23:45","last_updated_epoch":1739083500,"precip_in":0,"precip_mm":0,"pressure_in":29.99,"pressure_mb":1016,"temp_c":18.3,"temp_f":64.9,"uv":0,"vis_km":16,"vis_miles":9,"wind_degree":203,"wind_dir":"SSW","wind_kph":3.6,"wind_mph":2.2,"windchill_c":19.1,"windchill_f":66.5},"location":{"country":"USA","lat":33.5099,"localtime":"2025-02-08 23:58","localtime_epoch":1739084335,"lon":-112.08,"name":"Phoenix","region":"Arizona","tz_id":"America/Phoenix"}},"headers":{"Cache-Control":"public, max-age=180","Cdn-Cache":"EXPIRED","Cdn-Cachedat":"02/09/2025 06:59:04","Cdn-Edgestorageid":"1234","Cdn-Proxyver":"1.19","Cdn-Pullzone":"93447","Cdn-Requestcountrycode":"US","Cdn-Requestid":"0859189782e6a71b8814912e73b7627f","Cdn-Requestpullcode":"200","Cdn-Requestpullsuccess":"True","Cdn-Requesttime":"0","Cdn-Status":"200","Cdn-Uid":"8fa3a04a-75d9-4707-8056-b7b33c8ac7fe","Content-Type":"application/json","Date":"Sun, 09 Feb 2025 06:59:04 GMT","Server":"BunnyCDN-IL1-1207","Vary":"Accept-Encoding","X-Weatherapi-Qpm-Left":"4999962"},"status":200}
—————————————————————

When you prettify it, you get this:

—————————————————————

{
   "body":{
      "current":{
         "cloud":75,
         "condition":{
            "code":1003,
            "icon":"//cdn.weatherapi.com/weather/64x64/night/116.png",
            "text":"Partly cloudy"
         },
         "dewpoint_c":6.1,
         "dewpoint_f":42.9,
         "feelslike_c":18.3,
         "feelslike_f":64.9,
         "gust_kph":6.6,
         "gust_mph":4.1,
         "heatindex_c":19.4,
         "heatindex_f":66.9,
         "humidity":37,
         "is_day":0,
         "last_updated":"2025-02-08 23:45",
         "last_updated_epoch":1739083500,
         "precip_in":0,
         "precip_mm":0,
         "pressure_in":29.99,
         "pressure_mb":1016,
         "temp_c":18.3,
         "temp_f":64.9,
         "uv":0,
         "vis_km":16,
         "vis_miles":9,
         "wind_degree":203,
         "wind_dir":"SSW",
         "wind_kph":3.6,
         "wind_mph":2.2,
         "windchill_c":19.1,
         "windchill_f":66.5
      },
      "location":{
         "country":"USA",
         "lat":33.5099,
         "localtime":"2025-02-08 23:58",
         "localtime_epoch":1739084335,
         "lon":-112.08,
         "name":"Phoenix",
         "region":"Arizona",
         "tz_id":"America/Phoenix"
      }
   },
   "headers":{
      "Cache-Control":"public, max-age=180",
      "Cdn-Cache":"EXPIRED",
      "Cdn-Cachedat":"02/09/2025 06:59:04",
      "Cdn-Edgestorageid":"1234",
      "Cdn-Proxyver":"1.19",
      "Cdn-Pullzone":"93447",
      "Cdn-Requestcountrycode":"US",
      "Cdn-Requestid":"0859189782e6a71b8814912e73b7627f",
      "Cdn-Requestpullcode":"200",
      "Cdn-Requestpullsuccess":"True",
      "Cdn-Requesttime":"0",
      "Cdn-Status":"200",
      "Cdn-Uid":"8fa3a04a-75d9-4707-8056-b7b33c8ac7fe",
      "Content-Type":"application/json",
      "Date":"Sun, 09 Feb 2025 06:59:04 GMT",
      "Server":"BunnyCDN-IL1-1207",
      "Vary":"Accept-Encoding",
      "X-Weatherapi-Qpm-Left":"4999962"
   },
   "status":200
}

—————————————————————
The next thing for the user to do is select the particular items they're interested in seeing.

As an aside, in VCL I'd put this into a TMemo and set it to enable LineSelect and MultiSelect, but in WEB Core, the memos don't seem to offer that. So I made a TWebCheckList to select items. This thing trims off the blank space in front, so you see something that looks like this:

image
image413×503 7.9 KB

Now, here's what I'm curious about.

I'd like to allow the user to save these selections somewhere as a filter so when they run this query in the future, it would show only these values in a formatted display. Without any formatting you get this from the selected items:

"humidity": 39,
"last_updated": "2025-02-14 23:30",
"temp_f": 61,
"wind_dir": "W",
"wind_mph": 10.5,
"name": "Phoenix",
"region": "Arizona",

My question is ... how would you SAVE THESE SELECTIONS, so that when the query is run later, they get applied as a FILTER and it would let you display these data items in a formatted way?

Maybe something like:

Weather for: Phoenix Arizona as of: 2025-02-14 23:30
Temperature: 61
Humidity: 39
Wind Direction: W @ 10.5 MPH

It seems to me that, in general, you can't just save the key for each of the JSON pairs because they could be duplicated -- eg, maybe there's an array of items and you only want what's in one of them. So then you'd probably want to save the PATH to each item? If so, how would you do that so that it would be easy to take the list of saved paths and run them on the latest JSON string and then pass the values to another method that formats and displays the results.

Is there a library that lets you do this?

This doesn't seem like a very complex or uncommon type of problem to solve. It reminds me of what's done in XML data extraction and formatting for reports (which I've seen but have no direct experience with).

2

Ideally I think you'd want your selection tool to somehow reflect the same tree structure of the data so there's no ambiguity. Either by including the path when building the selection tool or using a different selection tool that has that kind of functionality.

The chore of creating a JSON object that is a filtered version of another isn't so difficult if you have the path in the selection criteria. The general approach would be to create a new JSON object and add all the selected criteria while looking up the original values from the other JSON.

In fact, when saving the selections from the list, it wouldn't be a bad idea to just save them as the JSON object with NULL values first, and then iterate over those to populate them whenever a new JSON object arrives.

3

Would a TreeView work? The question is how to handle arrays?

I Googled ways to find a path to a given JSON node, and several people suggested to use the .flat method then search for keywords. I guess it flattens out the tree.

Assuming the structure doesn't change over time (but it could expand), then you could save the paths by name to each target node. I'm just not sure how to go from a deep node to the root. Otherwise, you'd have to do a full tree traversal and probe all possible paths until you found the target node. That's really inefficient, but might not be noticeable for small JSON strings.

Maybe when you check a box, the logic could initiate such a search.

Or perhaps duplicate the JSON expression that's hidden where the node values are replaced with backlinks?

Assuming we can solve the path issue, what about the reporting part?

I can't believe nobody has solved this already. What am I missing? XML has had support for it for at least 20 years now!

4

This is where AI assistants can be particularly helpful.

Write me a Javascript function that will return a newline-terminated string that represents the path of each element in the JSON structure. Like a flattened version of the JSON where each line is an element of the JSON object that can uniquely locate it. Give an example of the JSON, the function, and an example of the function output.

Let's create a JavaScript function that flattens a JSON object into paths with their corresponding values. Here's a solution with detailed explanations:


function flattenJsonPaths(obj, prefix = '') {
    let result = '';
    
    // Helper function to handle different types of values
    function processValue(path, value) {
        // Handle null values
        if (value === null) {
            return `${path}: null\n`;
        }
        // Handle arrays
        if (Array.isArray(value)) {
            let arrayResult = '';
            value.forEach((item, index) => {
                arrayResult += flattenJsonPaths(item, `${path}[${index}]`);
            });
            return arrayResult;
        }
        // Handle objects
        if (typeof value === 'object' && value !== null) {
            return flattenJsonPaths(value, path);
        }
        // Handle primitive values (string, number, boolean)
        return `${path}: ${value}\n`;
    }

    // Iterate through each key in the object
    for (const key in obj) {
        if (Object.prototype.hasOwnProperty.call(obj, key)) {
            // Construct the current path
            const currentPath = prefix ? `${prefix}.${key}` : key;
            result += processValue(currentPath, obj[key]);
        }
    }
    
    return result;
}

// Example JSON object
const exampleJson = {
    name: "John",
    age: 30,
    address: {
        street: "123 Main St",
        city: "New York",
        coordinates: {
            lat: 40.7128,
            lng: -74.0060
        }
    },
    hobbies: ["reading", "gaming"],
    active: true,
    spouse: null
};

// Test the function
console.log(flattenJsonPaths(exampleJson));

Expected output:

name: John
age: 30
address.street: 123 Main St
address.city: New York
address.coordinates.lat: 40.7128
address.coordinates.lng: -74.0060
hobbies[0]: reading
hobbies[1]: gaming
active: true
spouse: null

Let's break down how this function works:

The flattenJsonPaths function takes two parameters:
    obj: The JSON object to flatten
    prefix: A string to track the current path (defaults to empty string)
We use a helper function processValue to handle different types of values:
    Null values are explicitly shown as "null"
    Arrays are processed by iterating through each element with its index
    Objects are recursively processed with updated paths
    Primitive values (strings, numbers, booleans) are directly formatted
The main function:
    Iterates through each key in the object
    Constructs the current path using dot notation for objects and bracket notation for arrays
    Uses the helper function to process each value
    Builds the result string with newline termination
The output format:
    Each line represents a unique path to a value
    Paths are constructed using dot notation (e.g., address.street)
    Array elements use bracket notation (e.g., hobbies[0])
    Each line ends with a newline character
    Values are separated from paths by ": "

This function handles:

Nested objects of any depth
Arrays (including nested arrays)
Null values
Primitive values (strings, numbers, booleans)
Mixed data types

You can test this function with different JSON structures, and it will maintain consistent path notation while preserving all values. The paths in the output can be used to uniquely identify and access any value in the original JSON structure.

1 Like