is it possible to pre-filter the JSON before issuing the result?

AlexandreZaytsev commented 2 years ago

HI is it possible to pre-filter the JSON before issuing the result? Below is an example of a response from the service of the access control system (admission) more than ~ 5000 events

question - in order not to load the system - is it possible to filter the JSON response by nested fields

for example Set Parsed = JsonConverter.ParseJSON (... where ... "Event.Message.Name" = my value) or Set Parsed = JsonConverter.ParseJSON (... where ... "Event.User.Token" = my value) etc.

? example JSON response

{
  "UserSID": "String Content",
  "NextToken": 9223372036854775807,
  "Event": [
{
  "ModificationStatus": 0,
  "Token": 8765,
  "AdditionalFields": [],
  "CardCode": "0000B3AFC1",
  "Department": {
    "Token": 1499,
    "Name": "Business Support"
    },
  "Device": {
    "Token": 5335,
    "Name": "400"},
  "EventFilterPassed": true,
  "FaceMaskDetected": false,
  "IconToken": 1248,
  "Issued": "\ / Date (1632983741000) \ /",
  "Message": {
    "Token": 218,
    "Name": "Logged in",
    "AccessGranted": true,
    "Code": 98,
    "Color": 0
    },
  "PhotoToken": 1247,
  "PhotoverificationFiltersPassed": [true, true, true, true],
  "Sender": {"Token": 5356, "Name": "PKK office - entrance"},
  "Temperature": 0,
  "User": {
    "Token": 1239,
    "Name": "Golikova Nadezhda Vladislavovna",
    "AdditionalFields": [],
    "EmployeeNumber": "11",
    "Post": "Office Manager"}
},
...
more than 5000 event records "Event": [
...
{
  "ModificationStatus": 0,
  "Token": 8769,
  "AdditionalFields": [],
  "CardCode": "0000B3AFBF",
  "Department": {"Token": 1459, "Name": "Accounting"},
  "Device": {
    "Token": 5335,
    "Name": "400"
    },
  "EventFilterPassed": true,
  "FaceMaskDetected": false,
  "IconToken": 1224,
  "Issued": "\ / Date (1632985647000) \ /",
  "Message": {
    "Token": 216,
    "Name": "Entry allowed",
    "AccessGranted": true,
    "Code": 8,
    "Color": 255
    },
  "PhotoToken": 1223,
  "PhotoverificationFiltersPassed": [true, true, true, true],
  "Sender": {
    "Token": 5356,
    "Name": "PKK Office - Entrance"
    },
  "Temperature": 0,
  "User": {
    "Token": 1219,
    "Name": "Andreeva Natalia Alexandrovna",
    "AdditionalFields": [],
    "EmployeeNumber": "10",
    "Post": "Accountant"
    }
},
  ],
  "EventColumns": ["String Content"],
  "PhotoverificationColumnCount": 2147483647,
  "PhotoverificationColumns": ["String Content"],
  "PhotoverificationRowCount": 2147483647
}

houghtonap commented 2 years ago

VBA-JSON reads the entire JSON dataset into memory objects so your concern about the dataset size is warranted. When dealing with large JSON datasets VBA-JSON is probably not the tool you want to use. Rather you should investigate using MySQL's JSON features or some other SQL or NoSQL database that handles JSON data.

Sometimes I use Excel to generate an HTTP request and get a JSON response. When the JSON response is large I import the data into MySQL and use MySQL's JSON features to either reduce the JSON data to what I need or create new columns from the JSON data. After that is done I re-import the data back into Excel to do whatever analysis is needed.

Hope that helps.

AlexandreZaytsev commented 2 years ago

yes, this is probably the way out, thank you

VBA-tools / VBA-JSON

is it possible to pre-filter the JSON before issuing the result? #212