Most Efficient Query Examples

[jeannekitchens]

@excelsior This is a use case for a partner. They have an integration with the Registry. They need unique CTIDs to match the organizations and credentials for outcome/metrics data they create in their system. They query the registry, import the CTID to their platform, include a link back to the related Finder page for each of their customers (i.e., CTDL Organization classes) and for each credential offered and owned by those organizations.
They need to be able to query the Registry dependably and they have a use case to find all of the credentials owned by an organization AND if those credentials are offered by other organizations.

Query: Show me all ceterms:Credential (subclasses) owned by and offered by a specific ceterms:CTID (they would first get the organization and use it’s CTID to find all credentials owned and offered by that organization.

Query: Show me all other ceterms:Organization (subclasses) that offer the credentials owned by the above organization using the ceterms:CTID for those credentials.

Need the best way to do these queries efficiently (following the Search API handbook formats https://credreg.net/registry/searchapi). There could be 100s of credentials owned by an organization that need to be checked to see if there are offered by other organizations.

[siuc-nate]

I'm not able to fully test these without the real CTID/data to work with, but these should get what you're looking for (I've included links to relevant portions of the search API handbook):

---

Show me all ceterms:Credential (subclasses) owned by and offered by a specific ceterms:CTID (they would first get the organization and use it’s CTID to find all credentials owned and offered by that organization.

All Credential subclasses owned by a given organization:
This one looks for anything that is a ceterms:Credential (or subclass thereof) that is also ceterms:ownedBy anything with the specified CTID.

{
	"@type": {
		"search:value": "ceterms:Credential",
		"search:matchType": "search:subClassOf"
	},
	"ceterms:ownedBy": {
		"ceterms:ctid": "[Organization CTID goes here]"
	}
}

---

Show me all other ceterms:Organization (subclasses) that offer the credentials owned by the above organization using the ceterms:CTID for those credentials.

You don't need the CTIDs for those credentials here, technically:
All organizations (+ subclasses) that offer any credentials owned by a given organization:
This one looks for anything that is a ceterms:Organization or (subclass thereof) that also either:

• has a ceterms:offers connection that references any Credential (or subclass thereof) which is also ceterms:ownedBy something with the specified CTID, OR which
• has a reverse ceterms:offeredBy connection (hence the ^ in front of the property name) that references any Credential (or subclass thereof) which is also ceterms:ownedBy something with the specified CTID

{
	"@type": {
		"search:value": "ceterms:Organization",
		"search:matchType": "search:subClassOf"
	},
	"search:termGroup": {
		"search:operator": "search:orTerms",
		"ceterms:offers": {
			"@type": {
				"search:value": "ceterms:Credential",
				"search:matchType": "search:subClassOf"
			},
			"ceterms:ownedBy": {
				"ceterms:ctid": "[Organization CTID goes here]"
			}
		},
		"^ceterms:offeredBy": {
			"@type": {
				"search:value": "ceterms:Credential",
				"search:matchType": "search:subClassOf"
			},
			"ceterms:ownedBy": {
				"ceterms:ctid": "[Organization CTID goes here also]"
			}
		}
	}
	
}

But if you still wanted to use the CTIDs for those credentials, then:
All organizations (+ subclasses) that offer any credentials in a list of CTIDs:
This will look for anything that is a ceterms:Organization (or subclass thereof) which:

• ceterms:offers anything with a CTID that is within the list of specified credential CTIDs, OR which:
• has a reverse ceterms:offeredBy connection to anything with a CTID that is within the list of specified credential CTIDs

{
	"@type": {
		"search:value": "ceterms:Organization",
		"search:matchType": "search:subClassOf"
	},
	"search:termGroup": {
		"search:operator": "search:orTerms",
		"ceterms:offers": {
			"ceterms:ctid": [
				"[CTID for credential 1]",
				"[CTID for credential 2]",
				"[CTID for credential 3]",
				"[etc.]"
			]
		},
		"^ceterms:offeredBy": {
			"ceterms:ctid": [
				"[CTID for credential 1]",
				"[CTID for credential 2]",
				"[CTID for credential 3]",
				"[etc.]"
			]
		}
	}
}

---

all of the credentials owned by an organization AND if those credentials are offered by other organizations.

All credentials owned by a given organization that are offered by one or more other organizations:
This will look for anything that is a ceterms:Credential (or subclass thereof) which is ownedBy anything with the specified CTID, and which:

• is ceterms:offeredBy anything with a CTID that is NOT the specified CTID, OR which:
• has a reverse ceterms:offers connection coming from anything with a CTID that is NOT the specified CTID.

In other words, it will find credentials owned by the specified organization and offered by any organization other than the specified organization.

We don't have a section for negation in the search API handbook, but it's basically controlled by using ! in front of the property name to say "any value that is NOT this value"

{
	"@type": {
		"search:value": "ceterms:Credential",
		"search:matchType": "search:subClassOf"
	},
	"ceterms:ownedBy": {
		"ceterms:ctid": "search:anyValue"
	},
	"search:termGroup": {
		"search:operator": "search:orTerms",
		"ceterms:offeredBy": {
			"!ceterms:ctid": "[Organization CTID goes here]"
		},
		"^ceterms:offers": {
			"!ceterms:ctid": "[Organization CTID goes here also]"
		}
	}
	
}

Note that if a credential is also offered by the specified organization, it will get filtered out by the above query. If you just want all credentials that are ceterms:ownedBy the specified organization and which are ceterms:offeredBy anyone/anything (regardless of whether that anyone is also the organization that owns it), then:

{
	"@type": {
		"search:value": "ceterms:Credential",
		"search:matchType": "search:subClassOf"
	},
	"ceterms:ownedBy": {
		"ceterms:ctid": "[Organization CTID goes here]"
	},
	"ceterms:offeredBy": "search:anyValue"
}

Hope that helps. Let me know if you have any questions. I can get you the HTML to put in the razor file to update the handbook to add a section for negation as well, if you need me to, but what's there already follows a pretty easy-to-copy pattern.

[jeannekitchens]

@siuc-nate thanks for working out all of these queries! Here's another, show me all of the credentials in the Registry that are offeredBy organizations other than the owner. The intent of the above is to find out if a credential owned by an organization is also offered by any other organizations.

[siuc-nate]

Sure thing!

The second-to-last example above should take care of that, either with or without the ceterms:ownedBy part, depending on whether you want to include that constraint. Without the ownedBy constraint, it would look like this:

{
	"@type": {
		"search:value": "ceterms:Credential",
		"search:matchType": "search:subClassOf"
	},
	"search:termGroup": {
		"search:operator": "search:orTerms",
		"ceterms:offeredBy": {
			"!ceterms:ctid": "[Organization CTID goes here]"
		},
		"^ceterms:offers": {
			"!ceterms:ctid": "[Organization CTID goes here also]"
		}
	}
	
}

That will look for anything that is a ceterms:Credential or subclass thereof, and which either:

• is ceterms:offeredBy any organization with a CTID other than the specified CTID, OR
• has a reverse ceterms:offers connection coming from any organization with a CTID other than the specified CTID.

[jeannekitchens]

@siuc-nate for the last example. The CTIDs are unknown. Show me all of the credentials that are offeredBy organizations that do not own it. How would we determine if there are credentials in the registry that are offered by organizations that are now the owner?

[siuc-nate]

I see. There are a few layers of complexity here:

• One or the other of ownedBy/offeredBy may not have any data
• You'd also need to account for the reverse owns/offers connections (which also may or may not have any data)
• You'd need some way to aggregate values together from ownedBy/owns and from offeredBy/offers before checking for matches

Additionally, all four of those properties are multi-value, so you'd have at least two variations of that query at a conceptual level, depending on how strict you want to be:

• Show me all of the credentials where there is at least one offeror who isn't an owner
• Show me all of the credentials where none of the offerors are owners
• Inverse use case: Show me all of the credentials where at least one offeror is also an owner
• Inverse use case: Show me all of the credentials where all offerors are also owners

You'd also need some way to indicate how to handle cases where there is no data for one or the other property so that a legitimate result that has a ceterms:ownedBy value doesn't get filtered out just because there isn't a reverse ceterms:owns connection, for example.

Unfortunately, none of that is supported by the current search API. Even if the above complexity didn't exist, there isn't a way to express "where [ownedBy] != [offeredBy]" in the current query structure.

The aggregation is probably the trickiest part, since you need to gather together values for both ownedBy and owns (reverse), then gather together the values for both offeredBy and offers (reverse), then check to see whether any/all/some/none of those CTIDs overlap. Some use cases may also need to deduplicate those values before checking.

I think you'd need some kind of sub-query structure/functionality to facilitate this, so that you can first get the requisite CTIDs and then reference them later in a normal query. You'd also need some kind of optional matching to run checks when data exists but avoid unintentionally filtering a result out when data doesn't exist.

Sub-queries and optional matching would unlock a boatload of functionality, but we'd want to be extremely careful about the syntax because those could also apply to myriad potential use cases and we don't want to paint ourselves into a corner with the semantics of how you'd structure those.

That said, here's an idea for what it could look like (being a little extra verbose for clarity's sake)

{
  "search:subQuery": [
    {
      "ceterms:ownedBy?": {
        "search:select": {
          "search:selectValueOf": "ceterms:ctid",
          "search:selectValueAs": "User-Defined Name for Owner CTIDs",
          "search:selectValueVia": "search:aggregation"
        }
      },
      "^ceterms:owns?": {
        "search:select": {
          "search:selectValueOf": "ceterms:ctid",
          "search:selectValueAs": "User-Defined Name for Owner CTIDs",
          "search:selectValueVia": "search:aggregation"
        }
      }
    }
  ],
  "@type": {
    "search:value": "ceterms:Credential",
    "search:matchType": "search:subClassOf"
  },
  "search:termGroup": [
    {
      "ceterms:offeredBy": {
        "!ceterms:ctid": {
          "search:referenceSubQueryResult": "User-Defined Name for Owner CTIDs"
        }
      },
      "^ceterms:offers?": {
        "!ceterms:ctid": {
          "search:referenceSubQueryResult": "User-Defined Name for Owner CTIDs"
        }
      }
    },
    {
      "ceterms:offeredBy?": {
        "!ceterms:ctid": {
          "search:referenceSubQueryResult": "User-Defined Name for Owner CTIDs"
        }
      },
      "^ceterms:offers": {
        "!ceterms:ctid": {
          "search:referenceSubQueryResult": "User-Defined Name for Owner CTIDs"
        }
      }
    }
  ]
}

The above:

1. Performs a sub-query (there could potentially be more than one in some cases, hence the array) which:

• optional-matches ceterms:ownedBy (to ensure this property is crawled if exists, but avoid skipping the rest of the subquery if it doesn't), selects the value of the owner's ceterms:ctid using aggregation (to ensure the value is actually an array of CTIDs and to permit appending values to that array from other owners), and maintains the aggregated CTIDs in a variable called User-Defined Name for Owner CTIDs
• optional-matches ceterms:owns in reverse (to also ensure this is crawled if it exists), selects the value of the owner's ceterms:ctid using aggregation, and maintains the aggregated CTIDs in the same variable (so that values for CTID get aggregated together regardless of whether they came via ownedBy or via owns in reverse)

2. Does a regular query which:

• looks for any result that has a @type of ceterms:Credential or one of its subclasses, and
• has a branching OR query (because the search:termGroup is an array here) which looks for results where:
  • There is a value for offeredBy where the CTID of the offering organization does not appear in the array of CTIDs that came from the sub-query (this is equivalent to passing the owner CTIDs in as an array in the examples in previous posts above), and optional-matches the reverse owns connection with the same CTID check, OR
  • There is a value for offers in reverse where the CTID of the offering organization does not appear in the array of CTIDs that came from the sub-query

The first part uses optional matching on both properties to ensure that data gets pulled from both connections (doing an OR would skip the second one once the first one was matched, while doing an AND would skip both if only one was present).

The last part looks redundant, but the purpose is to guarantee that there is a value for one or the other of offeredBy/offers (reverse), while also ensuring that both properties meet the criteria if both properties are present. Doing optional matches for both properties here would yield false positive results in the event that neither property was present.

If something like the above looks feasible, then we'd want to open separate issues for sub-queries and for optional matching (the two would be independent functionality). We would then need to thoroughly explore a bunch of use cases/edge cases and make sure the syntax/structure/etc works for all of them (the four use cases I outlined at the top of this post are probably a good starting point).

[jeannekitchens]

Thanks for this thorough analysis @siuc-nate ! Also begs the question to have dependable queries to find all credentials owned and/or offered by an organization, do we need to factor in the inverse properties so we have owns/offers and ownedBy/offeredBy? Are there situtation with the Registry where data could be published going from the credential to the organization?

[siuc-nate]

Sure thing.

do we need to factor in the inverse properties so we have owns/offers and ownedBy/offeredBy?

In most cases, it's enough to use a search:termGroup with "search:operator": "search:orTerms" as shown in several examples in earlier posts. It's a little clunky, but it does allow you to say "show me any credential with an offeredBy connection to this organization or with an offers connection coming from this organization" so it's gotten the job done up to this point.

But that kind of awkwardness in searching is one of the reasons I tend to hesitate when it comes to declaring bidirectional relationships at the schema level, since it means you then have to account for cases where data may be missing for one or the other.

Are there situtation with the Registry where data could be published going from the credential to the organization?

Do you mean via offeredBy/ownedBy? I tend to assume those properties will be more reliably accurate/available since they get published with every credential (it's a minimum data requirement to have at least one), whereas the owns/offers connection on the organization might not always be updated to reflect changes in credentials (and other things). But it's also possible that an organization may start (or stop) offering a credential and update its own record, but that credential's record might not ever get updated to reflect that change (e.g. the credential record is maintained by some other party). So ultimately, the only way to be sure is to check both.

Something Phil has brought up in the past is that in some systems (e.g. RDF triple stores), inferencing can be enabled to generate the "missing" relationships - but that would need to be carefully done to avoid making it look like QA relationships are always reciprocated (or any other similar cases where the data needs to be explicitly published in order to be valid).

Something like that might work here, but it would mean it's possible to get a search result that doesn't appear to match the query (e.g. you open the result for a credential and it doesn't show the offering organization - because the only connection was on the organization's side saying it "offers" the credential). That may be a worthwhile tradeoff for the sake of simpler queries.

If you went with that, you'd need to make it an option you can toggle at the query level though (e.g. "enable inferencing" along with skip/take, including/excluding description sets, etc.). That way you can still query strictly for what's in the actual records, not what the database was able to generate, as that kind of debugging/diagnostic query will always be a use case (e.g. "show me all the credentials that this organization says it offers, but they don't say they're offeredBy that organization").