Allow for direct validation of JSON files

[ASL-rmarshall]

The current capabilities of the CORE engine include a transformation from JSON to tabular formats. While this allows USDM rules to be implemented using existing functionality, it results in limitations for testing and a delayed performance. Based on preliminary tests using a direct JSON validation language (JQ), it is evident that some checks are more efficient when directly performed on the JSON files. For example, the check on unique identifiers across the whole USDM is very difficult when working with the tabular format we are using now. Another example is the possibility to check whether cross-referenced items are defined within the same study design.

Including JSON based checks in the rule editor and allowing the rule engine to perform those checks will open the path to the full direct JSON file checks in the future. For Phase 4 we propose to include this for 2-3 different rule types that are now blocked. In addition to unblocking these rules in phase 4, this will open the route for complete direct and fast JSON validation in future phases.

[ASL-rmarshall]

This is an example of an implementation of DDF00006 using jq:

jq '.. | select(.instanceType? == "Timing") | select(any([.windowLabel?, .windowLower?, .windowUpper?] | .[] | length;. > 0)) | select(all([.windowLabel?, .windowLower?, .windowUpper?] | .[] | length;. > 0) | not) | try error("For Timing \(.id) (\(.valueLabel)): One or more of the window attributes (windowLabel, windowLower, windowUpper) is missing: windowLabel=\(.windowLabel), windowLower=\(.windowLower), windowUpper=\(.windowUpper)") catch .' 
C:\CDISC\ddf\ddf4\testdata\CDISC_Pilot_Study_dirty.json

rewritten with some formatting:

jq '
  .. 
  | select(.instanceType? == "Timing") 
  | select(
      any(
        [.windowLabel?, .windowLower?, .windowUpper?] 
        | .[] 
        | length;. > 0
      )
    ) 
  | select(
      all(
        [.windowLabel?, .windowLower?, .windowUpper?] 
        | .[] 
        | length;. > 0
      ) 
      | not
    ) 
  | try error(
      "For Timing \(.id) (\(.valueLabel)): One or more of the window attributes (windowLabel, windowLower, windowUpper) is missing: windowLabel=\(.windowLabel), windowLower=\(.windowLower), windowUpper=\(.windowUpper)"
    ) catch .
' 
C:\CDISC\ddf\ddf4\testdata\CDISC_Pilot_Study_dirty.json

and this is an equivalent example using JSONata (the formatting is unnecessary - I added it for readability):

(
    $has_value := function($o, $v)
                  {
                    $v in $keys($o) 
                    ? $type($lookup($o,$v)) = "null" 
                        ? false 
                        : $length($lookup($o,$v)) > 0 
                    : false
                  } ;
    $filter(
            **,
            function($instance)
            {
                (
                    $instance.instanceType = "Timing"
                    and (
                            $has_value($instance,"windowLabel")
                         or $has_value($instance,"windowLower")
                         or $has_value($instance,"windowUpper")
                        ) 
                    and $not(    $has_value($instance,"windowLabel")
                             and $has_value($instance,"windowLower")
                             and $has_value($instance,"windowUpper")
                            )
                )
            }
       ).("For Timing " & $.id & " (" & $.valueLabel &
          "): One or more of the window attributes (windowLabel, windowLower, windowUpper) is missing: windowLabel=" &
          $.windowLabel & ", windowLower=" & $.windowLower & ", windowUpper=" & $.windowUpper)
)

Both return 3 error messages when run against CDISC_Pilot_Study_dirty.json:

"For Timing Timing_1 (2 weeks): One or more of the window attributes (windowLabel, windowLower, windowUpper) is missing: windowLabel=None, windowLower=null, windowUpper=null"
"For Timing Timing_2 (2 days): One or more of the window attributes (windowLabel, windowLower, windowUpper) is missing: windowLabel=-4..0 hours, windowLower=PT4H, windowUpper="
"For Timing Timing_3 (1 Day): One or more of the window attributes (windowLabel, windowLower, windowUpper) is missing: windowLabel=No upper/lower, windowLower=null, windowUpper=null"

[gerrycampion]

Do we need $has_value here or would this suffice:

$filter(
    **,
    function($instance) {
        $instance.instanceType = "Timing"
        and (
            $instance.windowLabel
            or $instance.windowLower
            or $instance.windowUpper
        ) 
        and $not(
            $instance.windowLabel
            and $instance.windowLower
            and $instance.windowUpper
        )
    }
).("For Timing " & $.id & " (" & $.valueLabel &
  "): One or more of the window attributes (windowLabel, windowLower, windowUpper) is missing: windowLabel=" &
  $.windowLabel & ", windowLower=" & $.windowLower & ", windowUpper=" & $.windowUpper
)

[ASL-rmarshall]

@gerrycampion I agree that your version is more succinct - you're right that $has_value is not required - but $has_value was acting a bit like a prespecified CORE operator. I'm wondering how you would specify a rule that relies on the implicit checking of attribute existence/value population that happens when you just specify the attribute name without any comparator.

[gerrycampion]

• What is the goal we are trying to accomplish with direct json language? Better representation, better speed, custom operators, or something else? Is the current tabular representation and queries a worse representation than the equivalent in the languages we have tested?
  • performance
  • tabular structure
  • cross references are cumbersome
• Is there a more complex rule we can use for testing other languages?
  • DDF00050 - https://jira.cdisc.org/browse/CORERULES-9445
  • DDF00076 - checking an unknown depth of children - current implementation works at top level, but not other levels because match datasets does not allow self-joins
  • DDF00124, DDF00131 - involve extracting values from json and using that information to do additional checks
• How to handle auxiliary data, like CT. Add as additional property in JSON?
  • schema checks are probably out of scope
  • CT checks could possibly be done using the tabular checks if not using direct JSON. Either way, new functionality is probably required
• Any preference between JQ, JSONATA, SQL, NOSQL?
• Should we bother keeping in YAML? NOSQL might be a good option for the rules in YAML because it uses JSON notation
• Amount of work to convert existing rules to new language?
  • Okay to keep current rules in existing language
• Do the translations programmatically, using AI, or manually?
• Do you think all the current rules are translatable?
  • May not be necessary to translate all rules. Some can be written in the tabular way and other in a json query language

[ASL-rmarshall]

@gerrycampion Here's some test data in JSON format for DDF00050:

• unit-test-coreid-DDF00050-positive.json
• unit-test-coreid-DDF00050-negative.json

I've run these both through a schema validator (no errors reported) and the CORE CLI (using the YAML rule definition from the editor), which gave these results:

• Positive:
  • Excel: CORE-Report-2025-04-03T11-44-01.xlsx
  • JSON: CORE-Report-2025-04-03T11-44-01.json
• Negative:
  • Excel: CORE-Report-2025-04-03T12-11-27.xlsx
  • JSON: CORE-Report-2025-04-03T12-11-27.json

[ASL-rmarshall]

JSONata version of DDF00050:

(**.studyDesigns)@$s.
    [
        $s.arms.populationIds[$not($ in $s.population.cohorts.id)].
        {
         "message": "The study arm references a cohort that was not defined within the population defined for the same study design as the study arm",
         "id": %.id,
         "name": %.name,
         "populationId": $
        }
    ]

DDF00050 was split out from DDF00049 because it was too complicated to check for both matching population ids and matching cohort ids. However, this simple using JSONata:

(**.studyDesigns)@$s.
    [
        $s.arms.populationIds[$ != $s.population.id and $not($ in $s.population.cohorts.id)].
        {
         "message": "The study arm references a populationId that does not match the id of any study population or study cohort defined within the same study design as the study arm",
         "parent_id": $s.id,
         "parent_entity": $s.instanceType,
         "id": %.id,
         "name": %.name,
         "populationId": $
        }
    ]

[ASL-rmarshall]

I had a play with Google BigQuery to experiment with SQL queries. This seemed to work for the combination of DDF00049 and DDF00050 (populationIds value does not match population.id or any population.cohorts.id value):

SELECT
  sd.id,
  arm.id,
  populationId
FROM
  UNNEST((
    SELECT
      study.versions[0].studyDesigns
    FROM
      `usdm-455810.CORE_Rules_Test_Data.DDF00050_negative`
    LIMIT
      1000)) AS sd,
  sd.arms AS arm,
  arm.populationIds AS populationId
WHERE
  populationId NOT IN (
  SELECT
    sd.population.id
  UNION ALL (
    SELECT
      id
    FROM
      UNNEST(sd.population.cohorts)))

However, I'm not sure how useful this is given that it's GoogleSQL syntax. If we're considering SQL or NOSQL, we'd have to figure out the environment in which to execute the queries.

[ASL-rmarshall]

I created an Azure Cosmos NoSQL database with a container that has an item for each JSON file. I uploaded the positive and negative test files for DDF00050 (after updating study.id to be "DDF00050_positive" or "DDF00050_negative" respectively).

I could then query these as follows for the combination of DDF00049 and DDF00050 (where c is the container):

SELECT c.study.id,
       ARRAY
       (
        SELECT
            sd.id as studyDesignId,
            arm.id as armId, 
            populationId
        FROM sd IN c.study.versions[0].studyDesigns
        JOIN arm IN sd.arms
        JOIN populationId IN arm.populationIds
        WHERE NOT
          (
            populationId = sd.population.id
            OR EXISTS 
              (
                SELECT cohort.id
                FROM cohort IN sd.population.cohorts
                WHERE populationId = cohort.id
              )
          )
       ) as issues
FROM c

which gives this as a result:

[
    {
        "id": "DDF00050_positive",
        "issues": []
    },
    {
        "id": "DDF00050_negative",
        "issues": [
            {
                "studyDesignId": "StudyDesign_2",
                "armId": "StudyArm_1",
                "populationId": "StudyCohort_1"
            },
            {
                "studyDesignId": "StudyDesign_2",
                "armId": "StudyArm_1",
                "populationId": "StudyCohort_2"
            }
        ]
    }
]

[gerrycampion]

Decisions to be made:

• What rule engine do we use?
  • JSONATA seems ideal - user friendly language, allows expressions, recursion, custom functions can be stored in a shared library, composition, speed
  • JQ piping is not very user-friendly
  • current engine is not flexible enough when it comes to recursive parent-child relationships and composition. Requires dev time for custom functions.
  • SQL/NOSQL (duckdb, cosmos, mongo, etc) is built for multi-document containers and not ideal for single documents. May be worth a look for non-json non-usdb engine in the future. For json, not worth the extra complication just for a json-like syntax of mongodb.
  • USDM can fit into a graph model, but graph ql's are not very user friendly
• Convert existing usdm rules to new engine?
  • Convert existing rules
    • Convert existing usdm rules from tabular to json rules #1133
    • Convert current ddf test data to json #1134
  • Do not convert existing rules
• Editing and Running usdm rules
  • vscode and run from jsonata commandline or simple script
    • allow for ct and schema to be accessible from core jsonata engine #1109
  • rule editor and run within CORE
    • dataservice and datareader for arbitrary json files #1104
    • rule type for jsonata or jq checks #1105
    • convert json validation output to the core json report format #1106
    • update rule editor to allow json as a source file #1107
    • update test rule endpoint to know how to handle json as a source file #1108
    • allow for ct and schema to be accessible from core jsonata engine #1109