Did you know that you can get JSON data directly from your SQL tables?

Let me introduce you to 2 useful SQL functions that are used to retrieve JSON data from SQL queries - JSON_ARRAY and JSON_OBJECT.
You can use those functions in the SELECT statement with other types of select items, and they can be specified in other locations where an SQL function can be used, such as in a WHERE clause

The JSON_ARRAY function takes a comma-separated list of expressions and returns a JSON array containing those values.

We're excited to continue to roll out new features to InterSystems IRIS Cloud SQL, such as the new Vector Search capability that was first released with InterSystems IRIS 2024.1. Cloud SQL is a cloud service that offers exactly that: SQL access in the cloud. That means you'll be using industry-standard driver technologies such as JDBC, ODBC, and DB-API to connect to this service and access your data. The documentation describes in proper detail how to configure the important driver-level settings, but doesn't cover specific third-party tools as - as you can imagine - there's an infinite number of them.

In this article, we'll complement that reference documentation with more detailed steps for a popular third-party data visualization tool that several of our customers use to access IRIS-based data: Microsoft Power BI.

There is an interesting new feature in the recently announced 2024.1 preview, JSON_TABLE. JSON_TABLE is one of a family of functions introduced by the 2016 version of the SQL Standard (ISO Standard, published in early 2017). It allows for JSON values to be mapped to columns and queried using SQL. JSON_TABLE is valid in the FROM clause of some SQL statements.

The syntax of JSON_TABLE is quite large, allowing for exceptional conditions where the provided JSON values don't match expectations, nested structures and so on.

Suppose you have an application that allows users to write posts and comment on them. (Wait... that sounds familiar...)

For a given user, you want to be able to list all of the published posts with which that user has interacted - that is, either authored or commented on. How do you make this as fast as possible?

Here's what our %Persistent class definitions might look like as a starting point (storage definitions are important, but omitted for brevity):

I have been walking through this with a few team members and as such I thought there might be others out there who could use it, especially if you work with HL7 & Ensemble/HealthConnect/HealthShare and never venture out past the Interoperability section.

We have a yummy dataset with recipes written by multiple Reddit users, however most of the information is free text as the title or description of a post. Let's find out how we can very easily load the dataset, extract some features and analyze it using features from OpenAI large language model within Embedded Python and the Langchain framework.

Hello Community,

SQL language remains the most practical way to retrieve information stored in a database.

The JSON format is very often used in data exchange.

It is therefore common to seek to obtain data in JSON format from SQL queries.

Below you will find simple examples that can help you meet this need using ObjectScript and Python code.

Hi Community,

In this article, I will introduce my application iris-VectorLab along with step by step guide to performing vector operations.

IRIS-VectorLab is a web application that demonstrates the functionality of Vector Search with the help of embedded python. It leverages the functionality of the Python framework SentenceTransformers for state-of-the-art sentence embeddings.

Application Features

• Text to Embeddings Translation.
• VECTOR-typed Data Insertion.
• View Vector Data
• Perform Vector Search by using VECTOR_DOT_PRODUCT and VECTOR_COSINE functions.
• Demonstrate the difference between normal and vector search
• HuggingFace Text generation with the help of GPT2 LLM (Large Language Model) model and Hugging Face pipeline

Introduction

There is a Link Procedure Wizard option within the Management Portal (System > SQL >Wizards > Link Procedure) which I had reliability issues with so I decided to use this solution instead.

In ObjectScript you have a wide collection of functions that return some value
typically:

set variable = $somefunction(param1,param2, ...)

There is nothing special about that.
But there is a set of functions that I classify as LEFT SIDED
The specialty of them is that you can use them also on the left of the equal operator
as a target in the SET command:

set $somefunction(param1,param2, ...) = value

FOREIGN TABLES is a rather fresh feature in IRIS (2023.?)
So I was motivated to try something new by own hands.
Documentation of Foreign Table from File is a good starting point.
Also the related promotional video is fine to start with.

The InterSystems IRIS has a series of facilitators to capture, persist, interoperate, and generate analytical information from data in XML format. This article will demonstrate how to do the following:

1. Capture XML (via a file in our example);
2. Process the data captured in interoperability;
3. Persist XML in persistent entities/tables;
4. Create analytical views for the captured XML data.

Capture XML data

The InterSystems IRIS has many built-in adapters to capture data, including the next ones:

Hi,

I was struggling with a procedure that was meant to receive a string and use it as a filter, I've found that since I want the procedure to do some data transformation and return a dataset, I needed to use objectScript language.

I've created the procedure using the SQL GUI in the portal, and everything works fine when calling the procedure from the SQL GUI but not through a JDBC connection here is the call "call spPatientOS('2024-04-07T12:35:32Z')"

InterSystems FAQ rubric

This can be achieved by using the CSV() procedure of the %SQL.Util.Procedures class.
Below is an example of usage code. (Assuming that the file test.csv is in c:\temp.)