Question
· Aug 4

SQL Search REST Interface Example

#JSON #REST API #SQL #HealthShare #InterSystems IRIS for Health #Health Connect #InterSystems IRIS #InterSystems Natural Language Processing (NLP, iKnow)

I am trying to help another group within our organization access a SQL Table that I have created to populate Epic Department Data within our environment and came across the ability to use SQL Seach REST Interface using iKnow.

However, I am having issues trying to get it to work via POSTMAN before I hand off the solution...

the POST URL... https://<servername>/api/iKnow/latest/TESTCLIN/table/osuwmc_Epic_Clarity.DepartmentMaster/search

where osuwmc_Epic_Clarity.DepartmentMaster is the table

In the body...

{"query":SELECT ID, Abbr, Name, ExternalName, PhoneNumber, ApptPhone, FaxNumber, Address1, Address2, City, Zip, Specialty, RevLocID, RevLocName, BuildingCategoryID, BuildingName, DepCategoryTypeID, DepType, Center, EAFParent FROM osuwmc_Epic_Clarity.DepartmentMaster WHERE ID = 300000000","index":"IDKEY","option":1}

but I am receiving..

{
    "errors": [
        {
            "error": "ERROR #5035: General exception Name 'Parsing error' Code '3' Data ''",
            "code": 5035,
            "domain": "%ObjectErrors",
            "id": "GeneralException",
            "params": [
                "Parsing error",
                "3",
                ""
            ]
        }
    ],
    "summary": "ERROR #5035: General exception Name &#39;Parsing error&#39; Code &#39;3&#39; Data &#39;&#39;",
    "warnings": [
        {
            "warning": "You are not accessing the latest version of this REST API. Use /api/iKnow/latest/* to access the most up-to-date version"
        }
    ]
}

Am I using this wrong? What might be the magic chant to get this to work without having to create a REST service?

Product version: IRIS 2025.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2025.1 (Build 230_2U) Wed Jun 4 2025 18:50:44 EDT [HealthConnect:8.2.2]
Discussion (14)5
Log in or sign up to continue
Benjamin De Boe · Aug 6

That REST API is indeed for querying iFind indices (hence the direct reference to an index you can provide) and the somewhat confusingly named "query" argument is actually to pass in the iFind search string. The API will then build a full SQL query for you and run it right away. 

Here's the OpenAPI spec for this endpoint (from self-documentation endpoint /api/iKnow/v1/USER/swagger):

  /table/{table}/search:
    post:
      operationId: /table/{table}/search-POST
      summary: |
        Search the given iFind index in the given table
      tags: ["iFind"]
      parameters:
        - $ref: '#/parameters/tableParam'
        - name: RequestBody
          description: JSON object with a list of query-specific arguments
          in: body
          schema:
            type: object
            properties:
              query:
                description: This is the only necessary parameter with no default value. The search terms to query against the iFind index.
                type: string
              index:
                description: the iFind index would be searched against, if you don't specify it, the first found iFind index would be used .
                type: string
              option:
                $ref: '#/definitions/OptionSpec'
              distance:
                description: only valid when option is fuzzy search (when option is 3)
                type: string
                example: "3"
              language:
                description: iKnow-supported language model to apply, for example "en"
                type: string
              includeText:
                description: whether the returned columns should include the column beging indexed by 'index'
                type: integer
                default: 0
                enum: [0, 1] 
              columns:
                description: specify the columns which also needed to be returned. For example, ["column1","column2"] 
                type: array
                example: []
                items:
                  type: string
              highlightSpec:
                $ref: '#/definitions/HighlightSpec'
                description: the parameters needed for Highlight
              rankSpec:
                $ref: '#/definitions/RankSpec'
                description: the parameters needed for Rank
              where:
                description: the valid SQL logical condition statement. For example, "column1 = ? AND column2 = ?"
                type: string
      responses:
        200:
          description: Successful response
          schema:
            type: object
            properties:
              rows:
                type: array
                default: []
                items:
                  type: object
0 0
Stephen Canzano · Aug 4

while it wouldnt be difficult to build a REST API to accept an arbitrary SQL statement and return results there are several things to consider.

1. a single SQL SELECT statement might return 10s of thousands of rows making the REST service problematic with respect to timeouts and payload response size

2. you will want to make sure SQL security so that someone isnt allowed to 

- perform a delete if they dont have access to do so

- select data from tables the dont have access to.

etc.

0 0
Stephen Canzano · Aug 4

The REST API is for

SQL Search

The InterSystems IRIS® SQL Search tool integrates with the InterSystems IRIS Natural Language Processor (NLP) to perform context-aware text search operations on unstructured data, with capabilities such as fuzzy search, regular expressions, stemming, and decompounding. Searching is performed using a standard SQL query with a WHERE clause containing special InterSystems SQL Search syntax.

Is your table using the NLP features?

0 0