Sorry, your browser does not support JavaScript or JavaScript is disabled. Please enable JavaScript or use another browser to have a better experience.
InterSystems IRIS Data Platform 2024.2
Embedded Language Development
InterSystems SQL Reference
SQL Aggregate Functions
JSON_ARRAYAGG
Failover Strategies for High Availability
Mirroring Overview
Mirroring Architecture and Planning
Configuring Mirroring
Managing Mirroring
Mirror Outage Procedures
Script Command Reference
Customization
Running the Terminal Application from the Command Line
Advanced Topics
Basics: Connecting an IDE
Source Control Integration
Classes Page
Routines Page
Class Reference
Unit Testing with %UnitTest
About %UnitTest
Creating Unit Tests
Execute Unit Tests
Viewing %UnitTest Results
Command-Line Routine Debugging
(Legacy) Using ^%ETN for Error Logging
(Legacy) Traditional Error Processing
ObjectScript Reference
Symbols & Abbreviations
Operators
Commands
Routine & Debugging Commands
Functions
Special Variables
Structured System Variables
Macro Preprocessor Directives
Identifiers
General System Limits
System Macros
Flags & Qualifiers
Regular Expressions
Translation Tables
Numeric Computing
ObjectScript Tutorial
The Basics
Overview of the ObjectScript Tutorial
Class Methods
Terminal
VS Code - ObjectScript
Do Command
Class Methods and Routines
Method Details
Passing Arguments by Reference
Return and Quit Commands
Write Command
Read Command
Set Command
Operator Precedence
Syntax
Common Errors
Write (again) and Kill Commands
If Construct
Simple Conditions
Complex Conditions
Hands-On Exercise 1
Class Compilation
Controlling Execution Flow
$Case Function
For Construct
While and Do/While Constructs
Try/Catch Construct, and Throw Command
Multiple Purpose Characters
Hands-On Exercise 2
Recap of Part 1
The Good Stuff
Strings
Storing a String
Concatenation Operator
Pattern Matching and Regular Expressions
Pattern Matching Examples
String Functions
String Functions, continued
Lists
Compare Pieces and Lists
Setting Substrings, Pieces, and List Items
More List Functions
Bitstrings
Dates
Hands-On Exercise 3
JavaScript Object Notation (JSON)
Arrays
Ordered Trees
Ordered Trees, continued
Value and Existence, and the $Data Function
Viewing and Killing Arrays
Globals
$Increment and $Sequence Functions
Storage Method
Storage Method, continued
Example of Globals
Hands-On Exercise 4
Recap of Part 2
Using Globals
$Order Function
$Order Function, continued
Working With Indexes
$Order Loop
Nested $Order Loops
Starting Focused $Order Loops
Ending Focused $Order Loops
Hands-On Exercise 5
Conversion Functions
$Get Function
Hands-On Exercise 6
Database Integrity
Lock Command
Lock Command, continued
Lock Command and $Test
Transaction Processing
Hands-On Exercise 7
InterSystems IRIS Objects and SQL
Object Methods
Object Storage
SQL Queries
Recap Of Part 3
RightTriangle Example Class and Exercise Solutions
Right Triangle Example Class
Exercise 1: First Data Entry Class
Exercise 2: Second Data Entry Class
Exercise 3: Third Data Entry Class
Exercise 4: Storage Method
Exercise 5: First Lookup Class
Exercise 6: Second Lookup Class
Exercise 7: Third Lookup Class
Install and Import Python Packages
Run Embedded Python
Call Embedded Python Code from ObjectScript
Call InterSystems IRIS from Embedded Python
Bridge the Gap Between ObjectScript and Embedded Python
Use Embedded Python in Interoperability Productions
Use the Flexible Python Runtime Feature
InterSystems IRIS Python Module Reference
Querying the Database
Defining and Using Stored Procedures
Storing and Using Stream Data (BLOBs and CLOBs)
SQL Users, Roles, and Privileges
Using Vector Search
SQL Settings Reference
Importing SQL Code
Importing and Exporting SQL Data
SQL Text Search
About SQL Search
Basics: SQL Search
Using SQL Search
SQL Search REST Interface
SQL Security
Optimizing SQL
SQL Performance Overview
How InterSystems IRIS Processes SQL Statements
Best Practices for Improving SQL Performance
Examine Query Performance
Analyze SQL Statements and Statistics
Interpreting an SQL Query Plan
SQL Performance Analysis Toolkit
Get SQL Performance Help
Defining a High-Performance Schema
Table Statistics for Query Optimizer
Define and Build Indexes
Choose an SQL Table Storage Layout
Define SQL Optimized Tables Through Persistent Classes
Configure SQL Performance Options
Configure Parallel Query Processing
Using Runtime Plan Choice
Configure Frozen Plans
Using Adaptive Mode to Improve Performance
Working with Cached Queries
Specify Optimization Hints in Queries
SQL Gateway Overview
Accessing a Database with the SQL Gateway
Connecting the SQL Gateway via JDBC
Connecting the SQL Gateway via ODBC
Using the SQL Gateway Programmatically
Introducing InterSystems IRIS Document Database
Using Document Database with ObjectScript
Using Document Database with REST
Using Document Database with Java
Using Document Database with .NET
Web Applications & APIs
About...
Web Gateway
Introduction
Access Built-in Web Apps with Your Web Server
Setup Overview
Install a Standalone Web Gateway
Extend Your Web Server Configuration
Choose URL Paths to Route
Overview of the Web Gateway Management Pages
Define Server Access Profiles
Define Application Access Profiles
Configure System-Wide Parameters
Protecting Web Gateway Connections to InterSystems IRIS
Managing and Monitoring the Web Gateway
CGI Environment Variables Passed by the Web Gateway
HTTP Response Headers Returned by the Web Gateway
Compressing the Response to Requests for CSP Forms (GZIP/ZLIB)
Implementing HTTP Authentication for Web Applications
Load Balancing, Failover, and Mirrored Configurations
Process Affinity and State-Aware Mode (Preserve Mode 1)
Web Gateway Registry in InterSystems IRIS
CSP.ini Parameter Reference
Using the NSD (Windows)
Using the NSD (UNIX®/Linux/macOS)
Alternative Options for Apache (UNIX®/Linux/macOS)
Locked-Down Apache
Alternative Options for IIS 7 or Later (Windows)
Web Server Considerations
Apache Considerations (UNIX®/Linux/macOS)
IIS Considerations (Windows)
REST and JSON
About...
REST Services
Introduction to Creating REST Services
Creating and Editing REST Services
Modifying the Implementation Class
Modifying the Specification Class
Securing REST Services
Listing and Documenting REST APIs
/api/mgmnt/ API Endpoints
OpenAPI Properties in Use
Creating a REST Service Manually
Using JSON
Using JSON in ObjectScript
Creating and Modifying Dynamic Entities
Iteration and Sparse Arrays
Working with Datatypes
Using the JSON Adaptor
Quick Reference for Dynamic Entity Methods
Requiring a Username and Password
Controlling the XML Types
Controlling the Namespaces of the Schema and Types
Including Documentation for the Types
Adding Namespace Declarations to the SOAP Envelope
Checking for Required Elements and Attributes
Controlling the Form of Null String Arguments
Controlling the Message Name of the SOAP Response
Overriding the HTTP SOAP Action and Request Message Name
Specifying Whether Elements Are Qualified
Controlling Whether Message Parts Use Elements or Types
Controlling Use of the xsi:type Attribute
Controlling Use of Inline References in Encoded Format
Specifying the SOAP Envelope Prefix
Restricting the SOAP Versions Handled by a Web Service
Sending Responses Compressed by gzip
Defining a One-Way Web Method
Adding Line Breaks to Binary Data
Adding a Byte-Order Mark to the SOAP Messages
Customizing the Timeout Period
Using Process-Private Globals to Support Very Large Messages
Customizing Callbacks of a Web Service
Specifying Custom Transport for a Web Service
Defining Custom Processing in a Web Service
Web Client Options
Modifying a Web Client
Disabling Keep-Alive for a Web Client
Controlling the Form of Null String Arguments
Controlling the Client Timeout
Using a Proxy Server
Setting HTTP Headers
Specifying the HTTP Version to Use
Disabling SSL Server Name Checking
Controlling Use of the xsi:type Attribute
Controlling Use of Inline References for Encoded Format
Specifying the Envelope Prefix
Adding Namespace Declarations to the SOAP Envelope
Sending Responses Compressed by gzip
Quoting the SOAP Action (SOAP 1.1 Only)
Treating HTTP Status 202 Like Status 200
Defining a One-Way Web Method
Adding Line Breaks to Binary Data
Adding a Byte-Order Mark to the SOAP Messages
Using Process-Private Globals When Parsing
Creating Custom SOAP Messages
Specifying Custom HTTP Requests
Customizing Callbacks of a Web Client
Specifying Custom Transport from a Web Client
Specifying Flags for the SAX Parser
Using the WS-Security Login Feature
Using HTTP Authentication
SOAP Security
Securing Web Services with SOAP
Setup and Other Common Activities
Creating and Using Policies
WS-Policy Configuration Class Details
Adding Security Elements Manually
Adding Timestamps and Username Tokens
Encrypting the SOAP Body
Encrypting Security Header Elements
Adding Digital Signatures
Using Derived Key Tokens for Encryption and Signing
Combining Encryption and Signing
Validating and Decrypting Inbound Messages
Creating Secure Conversations
Using WS-ReliableMessaging
Creating and Adding SAML Tokens
Troubleshooting Security Problems
Details of the Security Elements
What Is %Library.File?
Query Directories and Drives
File and Directory Properties and Attributes
File and Directory Names
Work with Directories
Work with Files
Work with the %File Object
%File Example
XML Tools
Introduction
Considerations When Using the XML Tools
Reading & Validating
Using %XML.TextReader
Importing into Objects
Representing as DOM
Writing XML from Objects (Basics)
Writing XML (Details)
Encryption
Signing
XPath Expressions
XSLT Transformations
Customizing Parser
Classes from Schemas
Schemas from Classes
Examining Namespaces and Classes
XML Standards
XML Background
XML Projections
Introduction to Object-XML Projections
Projecting Objects to XML
Controlling Transformations of Values
Handling Empty Strings and Null Values
Controlling the XML Element and Attribute Names
Specifying Namespaces for Elements and Attributes
Controlling the Projection to XML Schemas
Advanced Options for XML Schemas
Special Topics
Summary of XML Projection Parameters
Internet Utilities
HTTP Request
HTTP Response
Email Support
Creating Email Messages
Sending via SMTP
Retrieving via POP3
Working with Received Email
IBM WebSphere MQ
External Messaging Platforms
Using the Messaging APIs
Kafka
RabbitMQ
Amazon SNS
Amazon SQS
REST API for Source Code
Introduction
Tutorial
Reference
API Index
Tools Index
Introduction to InterSystems External Servers
Working with External Languages
Managing External Server Connections
Customizing External Server Definitions
InterSystems External Server Requirements
Quick Reference for the ObjectScript $system.external Interface
About...
JDBC for Relational Access
Demo: Connect with JDBC
Demo: Java and XEP
Using Java
Java with InterSystems Overview
InterSystems Java Connectivity Options
Using the JDBC Driver
Configuration and Requirements
JDBC Fundamentals
JDBC Quick Reference
Native SDK for Java
Introduction to the Java Native SDK
Calling ObjectScript Methods and Functions from Java
Using Java Inverse Proxy Objects
Working with Global Arrays
Transactions and Locking
Java Native SDK Quick Reference
Persisting Java Objects with XEP
Using XEP with Java Overview
Introduction to XEP
Using XEP Event Persistence
XEP Quick Reference
InterSystems Implementation Reference for Third Party Software
Overview of Third Party Software
JDBC Driver Support
Python DB-API Support
Hibernate Support
Introduction to Using .NET with InterSystems Software
Connecting to the Database with .NET
.NET Requirements and Configuration
Using the ADO.NET Managed Provider
Quick Reference for the .NET Managed Provider
Native SDK for .NET
Native SDK for .NET Overview
Introduction to the .NET Native SDK
Calling ObjectScript Methods and Functions from .NET
Using .NET Inverse Proxy Objects
Working with Global Arrays
Transactions and Locking
Native SDK for .NET Quick Reference
Persisting .NET Objects with XEP
Introduction to XEP for .NET
Using XEP Event Persistence with .NET
Quick Reference for XEP .NET Classes
About...
Using the InterSystems ODBC Driver
Document Overview
Getting Started
Defining an ODBC Data Source on Windows
Defining an ODBC Data Source on UNIX®
ODBC Installation and Validation on UNIX® Systems
ODBC Support for Python and Node.js
Logging and Environment Variables
Introduction to the Native SDK for Python
Calling Database Methods and Functions from Python
Controlling Database Objects from Python
Accessing Global Arrays with Python
Managing Transactions and Locking with Python
Using the Python DB-API
Native SDK for Python Quick Reference
Introduction to the Native SDK for Node.js
Calling Class Methods from Node.js
Controlling Database Objects from Node.js
Accessing Global Arrays with Node.js
Managing Transactions and Locking with Node.js
Native SDK for Node.js Quick Reference
The InterSystems $ZF Callout Interface
Overview of the $ZF Callout Functions
Running Programs or System Commands with $ZF(-100)
Creating an InterSystems Callout Library
Invoking Callout Library Functions
InterSystems $ZF Callout Quick Reference
Introduction to Interoperability Productions
Connectivity Options
Business Processes and Business Logic
Other Production Options
Preparing to Create Productions
Production Roadmap
Planning a Deployment
Production Best Practices
Best Practices for Production Development
Design Model for a Routing Production
Converting Interfaces to Production Elements
Managing Databases for Productions
Developing Productions
About...
ObjectScript Productions
Introduction
Coding Business Hosts
Defining Messages
Business Service Class
Business Process Class
Business Operation Class
Alert Processor
Data Transformations
Business Metrics
Enterprise Message Bank
Record Mapper
Complex Record Mapper
Record Map Batch
Other Development Tasks
Test and Debug
Deploy Production
Production Lifecycle
Java, Python, & .NET Productions
Introduction
Getting Started
About Business Hosts and Adapters
PEX Messaging
Inbound Adapters
PEX Outbound Adapters
Business Services
PEX Business Processes
PEX Business Operations
Registering a PEX Component
Connecting with External Language Servers
PEX API Reference
BPL and DTL
Developing BPL Processes
Introduction
Listing the Business Processes
Creating
Editing
Variables Available in BPL
Syntax Rules
List of BPL Elements
Handling Errors in BPL
BPL Business Process Example
Developing DTL Transformations
Introduction to DTL Tools
Creating Data Transformations
Syntax Rules
Adding Assign Actions
Adding Other Actions
Testing Data Transformations
BPL and DTL Reference
BPL Elements
DTL Elements
Enterprise Service Bus and Registry Overview
Accessing the Public Service Registry through the Public REST API
Administering the Public Service and External Service Registries
Configuring an ESB
Configuring ESB Services and Operations
Configuring an InterSystems IRIS System and Creating a Namespace
Configuring a Web Application for a Pass-through Business Service
Pass-through Service and Operation Walkthrough
Service Registry Public API Reference
Workflow
Overview
Developing a Workflow
Including Custom Features in a Workflow
Testing a Workflow
Exploring the Workflow Sample
Available Workflow Metrics
Push Notifications
Push Notifications Overview
Configuring and Using Push Notifications
System Default Settings
Interoperability Settings
Configuring a Mirror Virtual IP as the Network Interface
Enterprise Message Bank
Identifying Enterprise Systems for Viewing and Monitoring
Creating Dashboards
Settings Reference
Managing Productions
Introduction
Starting and Stopping
Purging Data
Using the Archive Manager
Managing Workflows
Publish and Subscribe
Controlling Data Storage
Security Roles and Resources
Menu Items
Monitoring Productions
Core Concepts When Monitoring Interoperability Productions
Monitoring All Namespaces
Monitoring a Production
Viewing, Searching, and Managing Messages
Event Log
Tracing
Business Rule Log
Business Process Instances
I/O Archive
Viewing Messages from Multiple Productions
Enterprise Message Viewer
Enterprise Message Bank
Interface Maps
Interface References
Alerts
Activity Volume
Port Usage
Testing Productions
Testing Productions Tutorial
Testing Productions
TestControl
Callback Methods
Auxiliary Methods
Parameters and Properties
Creating a Test - Summary
Executing a Test
Example Overview
Example: Example Production
Example: Production Test
Example: OnAfterProductionStart
Example: CheckResults
Example: CheckErrors
Example: Executing the Test
Recap
Creating Tutorial.ExampleOperation
JMS Overview
Configuring and Using JMS Business Services and Operations
Creating Custom JMS Services and Operations Using the Adapter
IBM WebSphere MQ
Introduction
Inbound Adapter
Outbound Adapter
Troubleshooting IBM WebSphere MQ Adapters
Settings Reference
Introduction
MQTT Adapters
Passthrough
Settings Reference
REST Services and Adapters
REST Services
REST Operations
SOAP Services and Web Clients
Introduction
Web Services
Web Clients
Configuring the Production
Inbound Adapter (Alternative)
Older Web Service Variation
Settings Reference
SAP Java Connector
Overview
Setup Tasks
Using the SAP Java Connector
SQL Adapters
Introduction
Using an SQL Business Service
Using an SQL Business Operation
More About Messages
More About Procedures
Custom SQL Business Services
Custom SQL Business Operations
Creating Adapter Methods
Using Result Sets (SQL Adapters)
Using Snapshots (SQL Adapters)
TCP Adapters
Inbound Adapters
Outbound Adapters
Special Topics
Settings Reference
Managed File Transfer
Introduction
Prerequisites & Configuration
Configuring the Production
MFT API
Introduction to Business Intelligence
Introduction to the Business Intelligence User Interfaces
Introduction to the Other Business Intelligence Tools
Business Intelligence Glossary
BI Modeling
Overview of InterSystems IRIS Business Intelligence Models
Basic Concepts
Summary of Model Options
Principles and Recommendations
Defining Models for InterSystems Business Intelligence
Compiling and Building Cubes
Defining Dimensions, Hierarchies, and Levels
Details of Defining Levels
Defining Properties
Defining Measures
Defining Listings
Defining Listing Fields
Defining Calculated Members
Defining a Named Set
Defining Subject Areas
Defining Listing Groups
Reference Information for Cube Classes
Reference Information for Subject Area Classes
Details for the Fact and Dimension Tables
BI Advanced Modeling
Defining Computed Dimensions
Defining Shared Dimensions and Compound Cubes
Defining Cube-Cube Relationships
Using Text Analytics in Cubes
Defining Term Lists
Defining Worksheets
Defining Quality Measures
Defining Basic KPIs
Defining KPIs with Filters and Listings
Defining Advanced KPIs
Defining Plug-ins
Using Cube Inheritance
Defining Intermediate Expressions
Other Options
Reference Information for KPI and Plug-in Classes
Generating Secondary Cubes for Use with Text Analytics
Using Dashboards and the User Portal
Introduction to the User Portal
Using the Portal Features
Orientation to Dashboards
Using Dashboards
Using the Pivot Analysis Window
BI Dashboards
Introduction to Dashboards
Creating and Modifying Dashboards
Specifying Common Widget Options
Adding Pivot Table Widgets
Adding Chart Widgets
Customizing the Appearance of a Chart Widget
Adding Meter Widgets
Adding Scorecard Widgets
Adding Other Widgets
Defining and Using Themes
Adding Widget Controls
Implementing InterSystems IRIS Business Intelligence
Overview of Business Intelligence
Initial Steps
Settings
Data Connectors
Performance Tips
Custom Actions
Accessing Dashboards from Your Application
Keeping the Cubes Current
Using Cube Synchronization
Using the Cube Manager
Executing Queries Programmatically
Localization
Packaging Business Intelligence Elements into Classes
Portlets
Additional Steps
Controlling Access
Cube Versions
How the Analytics Engine Works
MDX Performance Utility
Diagnostics
Other Export/Import Options
Disaster Recovery
BI Analyzer
Introduction to the Analyzer
Creating Pivot Tables
Filtering Pivot Tables
Defining Calculated Elements
Defining and Using Pivot Variables
Customizing Pivot Tables
Working with Pivot Tables
Performing Ad Hoc Analysis
Configuring InterSystems IRIS for PDF Output
Using MDX
Business Intelligence and MDX
Basic MDX Queries
Working with Levels
Working with Dimensions and Hierarchies
Working with Sets
Tuples and Cubes
Filtering a Query
Adding Summaries
Creating Calculated Measures and Members
MDX Reference
Basic Rules
Expression Types (MDX)
MDX Statements and Clauses
MDX Functions
Intrinsic Properties (MDX)
NOW Member for Time Levels
Quick Function Reference (MDX)
Power BI
BI Clients
Using Business Intelligence
DeepSee.js
Business Intelligence REST API
BI Tutorial
Business Intelligence Tutorial: Getting Started
Introduction to Cube Elements
Creating a Cube
Expanding the Cube Definition
Creating Subject Areas
Creating & Packaging Pivot Tables & Dashboards
Creating an NLP Environment Manually
Loading Text Data Programmatically
Performance Considerations when Loading Texts
NLP Queries
Semantic Attributes
Stemming
Skiplists
Filtering Sources
Text Categorization
Dominance and Proximity
Custom Metrics
Smart Matching: Creating a Dictionary
Smart Matching: Using a Dictionary
User Interfaces
InterSystems IRIS Natural Language Processing (NLP) Tools
iKnow Web Services
KPIs and Dashboards
Customizing NLP
Language Identification
Domain Parameters
Predictive Modeling Markup Language
Unstructured Information Management Architecture
Using UIMA Support
InterSystems Reports
InterSystems IRIS Adaptive Analytics
Optional
— Specifies that
JSON_ARRAYAGG
returns a JSON array containing all values for
string-expr
. This is the default if no keyword is specified.
DISTINCT
Optional
— A
DISTINCT clause
that specifies that
JSON_ARRAYAGG
returns a JSON array containing only the unique
string-expr
values. DISTINCT can specify a
BY(col-list)
subclause, where
col-list
can be a single field or a comma-separated list of fields.
string-expr
An SQL expression that evaluates to a string. Usually the name of a column from the selected table.
%FOREACH(
col-list
)
Optional
— A column name or a comma-separated list of column names. See
SELECT
for further information on %FOREACH.
%AFTERHAVING
Optional
— Applies the condition found in the
HAVING
clause.
Description
The
JSON_ARRAYAGG
aggregate function
returns a JSON format array of the values in the specified column. For further details on JSON array format, refer to the
JSON_ARRAY
function.
A simple
JSON_ARRAYAGG
(or
JSON_ARRAYAGG ALL
) returns a JSON array containing all the values for
string-expr
in the selected rows. Rows where
string-expr
is the empty string ('') are represented by ("\u0000") in the array. Rows where
string-expr
is NULL are not included in the array. If there is only one
string-expr
value, and it is the empty string (''),
JSON_ARRAYAGG
returns the JSON array
["\u0000"]
. If all
string-expr
values are NULL,
JSON_ARRAYAGG
returns an empty JSON array
[]
.
A
JSON_ARRAYAGG DISTINCT
returns a JSON array composed of all the different (unique) values for
string-expr
in the selected rows:
JSON_ARRAYAGG(DISTINCT col1)
. The NULL
string-expr
is not included in the JSON array.
JSON_ARRAYAGG(DISTINCT BY(col2) col1)
returns a JSON array containing only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.
The
JSON_ARRAYAGG
string-expr
cannot be a stream field. Specifying a stream field results in an SQLCODE -37.
Data Values Containing Escaped Characters
Double Quote: If a
string-expr
value contains a double quote character ("),
JSON_ARRAYAGG
represents this character using the literal escape sequence
\"
.
Backslash: If a
string-expr
value contains a backslash character (\),
JSON_ARRAYAGG
represents this character using the literal escape sequence
\\
.
Single Quote: When a
string-expr
value contains a single quote as a literal character, InterSystems SQL requires that this character must be escaped by doubling it as two single quote characters (
''
.
JSON_ARRAYAGG
represents this character as a single quote character
'
.
Maximum JSON Array Size
The default
JSON_ARRAYAGG
return type is VARCHAR(8192). This length includes the JSON array formatting characters as well as the field data characters. If you anticipate the value returned will need to be longer than 8192, you can use the CAST function to specify a larger return value. For example,
CAST(JSON_ARRAYAGG(value)) AS VARCHAR(12000))
. If the actual JSON array returned is longer than the
JSON_ARRAYAGG
return type length, InterSystems IRIS truncates the JSON array at the return type length without issuing an error. Because truncating a JSON array removes its closing ] character, this makes the return value invalid.
JSON_ARRAYAGG and %SelectMode
You can use the
%SelectMode
property to specify the data display values for the elements in the JSON array: 0=Logical (the default), 1=ODBC, 2=Display. If the
string-expr
contains a %List structure, the elements are represented in ODBC mode separated by a comma, and in Logical and Display mode with %List format characters represented by \ escape sequences. Refer to
$ZCONVERT
“Encoding Translation” for an table listing these JSON \ escape sequences.
JSON_ARRAYAGG and ORDER BY
The
JSON_ARRAYAGG
function combines the values of a table column from multiple rows into a JSON array of element values. Because an
ORDER BY
clause is applied to the query result set after all aggregate fields are evaluated,
ORDER BY
cannot directly affect the sequence of values within this list. Under certain circumstances,
JSON_ARRAYAGG
results may appear in sequential order, but this ordering should not be relied upon. The values listed within a given aggregate result value cannot be explicitly ordered.
Related Aggregate Functions
LIST
returns a comma-separated list of values.
%DLIST
returns an InterSystems IRIS list containing an element for each value.
XMLAGG
returns a concatenated string of values.
Examples
This example returns a JSON array of all values in the Home_State column of the Sample.Person table that start with the letter “A”:
SELECT JSON_ARRAYAGG ( Home_State )
FROM Sample . Person
WHERE Home_State %STARTSWITH 'A'
Note that this JSON array contains duplicate values.
The following example returns a host variable containing a JSON array of all of the distinct (unique) values in the Home_State column of the Sample.Person table that start with the letter “A”:
SELECT DISTINCT JSON_ARRAYAGG ( Home_State ) AS DistinctStates
FROM Sample . Person
WHERE Home_State %STARTSWITH 'A'
The following example creates a JSON array of all of the values found in the Home_City column for each of the states, and a count of these city values by state. Every Home_State row contains a JSON array of all of the Home_City values for that state. These JSON arrays may include duplicate city names:
SELECT Home_State ,
COUNT ( Home_City ) AS CityCount ,
JSON_ARRAYAGG ( Home_City ) AS ArrayAllCities
FROM Sample . Person
GROUP BY Home_State
Perhaps more useful would be a JSON array of all of the
distinct
values found in the Home_City column for each of the states, as shown in the following example:
SELECT DISTINCT Home_State,
COUNT(DISTINCT Home_City) AS DistCityCount,
COUNT(Home_City) AS TotCityCount,
JSON_ARRAYAGG(DISTINCT Home_City) AS ArrayDistCities
FROM Sample.Person GROUP BY Home_State
Note that this example returns integer counts of both the distinct city names and the total city names for each state.
The following Dynamic SQL example uses the %SelectMode property to specify the ODBC display mode for the JSON array of values returned by the DOB date field:
SET myquery = 2
SET myquery ( 1 ) = "SELECT JSON_ARRAYAGG(DOB) AS DOBs "
SET myquery ( 2 ) = "FROM Sample.Person WHERE Name %STARTSWITH 'A'"
SET tStatement = ##class ( %SQL . Statement ) . %New ( )
SET tStatement . %SelectMode = 1
SET qStatus = tStatement . %Prepare ( . myquery )
IF qStatus '= 1 { WRITE "%Prepare failed:" DO $System . Status . DisplayError ( qStatus ) QUIT }
SET rset = tStatement . %Execute ( )
DO rset . %Display ( )
WRITE ! , "End of data" The following example uses the %FOREACH keyword. It returns a row for each distinct Home_State containing a JSON array of age values for that Home_State.
SELECT DISTINCT Home_State,
JSON_ARRAYAGG(Age %FOREACH(Home_State))
FROM Sample.Person
WHERE Home_State %STARTSWITH 'M'
The following example uses the %AFTERHAVING keyword. It returns a row for each Home_State that contains at least one Name value that fulfills the HAVING clause condition (a name that begins with “M”). The first JSON_ARRAYAGG function returns a JSON array of all of the names for that state. The second JSON_ARRAYAGG function returns a JSON array containing only those names that fulfill the HAVING clause condition:
SELECT Home_State,
JSON_ARRAYAGG(Name) AS AllNames,
JSON_ARRAYAGG(Name %AFTERHAVING) AS HavingClauseNames
FROM Sample.Person GROUP BY Home_State
HAVING Name LIKE 'M%' ORDER BY Home_State
© 2024 InterSystems Corporation, Cambridge, MA. All rights reserved.
PrivacyOpens in a new tab
& TermsOpens in a new tab GuaranteeOpens in a new tab
AccessibilityOpens in a new tab
Content date/time: 2024-10-25 03:47:37
