• This example is using a TEMPORARY Table based on Process Private Globals.
• You can access the content of the table just from the process that created the table. 
  Not from any other process.
• From SMP you see the Table Definition from any Namespace because ot the %ZZ. package.
  so the table is FOUND but EMPTY
• Though your SELECT from SMP runs in a totally different process with anything but your Process Private Global
• You have to get your final SELECT and generating your temporary table into the same Process,

• To achieve this in SMP, declare your ClassMethod As SqlProcedure like this:

  ClassMethod Export(nspace = "", query As %String = "") As %Integer [ SqlName = ZZEXPORT, SqlProc ]
  {
  	new $namespace
  	;; and so on

• Then you can run it from SMP using this SQLprocedure as Static WHERE condition.

  SELECT * from %ZZ.resultset
  WHERE ZZEXPORT('SAMPLES',
  'SELECT TOP 5 Description, ErrorStatus, Priority from ENS.MessageHeader')=0

• For Static WHERE Conditions see my article just 6 years back.

• Using a TEMPORARY table avoids problems if running in more than 1 instance in parallel. 

  Sorry, 
  The new Editor is not so well-suited for long line replies. I dislike it.

I composed a variant that doesn't require any code in ENS.
It can all run from your ONS namespace.
All you need is a valid SQL SELECT statement
First, the utility class:

Class User.ZZ [ Abstract ]
 {

ClassMethod Export(nspace = "", query = "") As %Integer
{
	new $namespace
	zn:$l(nspace) nspace
	&SQL(DROP TABLE %ZZ.resultset)
	; default just for demo
		set:'$l(query) query="SELECT TOP 5 ssn, home_state,name from Sample.employee"

    ;
	set sql=2,
		sql(1)="CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as ",
		sql(2)=query
	set statement=##class(%SQL.Statement).%New()
	set result=statement.%ExecDirect(.statement,.sql)
	quit result.%SQLCODE
} }

a quick demo:

USER>set select="SELECT TOP 3 home_state,name,SSN from Sample.employee"
USER>w ##class(ZZ).Export("SAMPLES",select)
0
USER>:sql
SQL Command Line Shell
----------------------------------------------------
[SQL]USER>>select * from %ZZ.resultset
5.      select * from %ZZ.resultset 
| Home_State | Name | SSN |
| -- | -- | -- |
| NC | Emerson,Xavier O. | 217-28-9642 |
| CO | Malkovich,Bob V. | 226-71-5540 |
| UT | Joyce,Juanita V. | 396-77-7486 |
 
3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0579s/39,198/181,087/0ms
 execute time(s)/globals/cmds/disk: 0.0002s/4/815/0ms
 query class: %sqlcq.USER.cls73
----------------------------------------------------------

[SQL]USER>>cos ZN "SAMPLES"
[SQL]SAMPLES>>select * from %ZZ.resultset
6.      select * from %ZZ.resultset

| Home_State | Name | SSN |
| -- | -- | -- |
| NC | Emerson,Xavier O. | 217-28-9642 |
| CO | Malkovich,Bob V. | 226-71-5540 |
| UT | Joyce,Juanita V. | 396-77-7486 |
 
3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0552s/39,198/181,087/0ms
 execute time(s)/globals/cmds/disk: 0.0002s/4/815/0ms
 query class: %sqlcq.SAMPLES.cls92
--------------------------------------------------
[SQL]SAMPLES>>q
 
SAMPLES>

hope this helps:
The temporary table is visible from both namespaces
And the utiltiy is not tied to a specific query or table
All just runs and exists in your process.
Also no traces in the Journal 
 

I was just running it without any problem in Windows Docker Desktop
Some preparation:

• docker pull intersystemsdc/iris-community
  • 28 days old !
• set DOCKER_BUILDKIT=1
• docker compose build --no-cache

No problems with build

Start also OK
 

I fail to understand your question.

For the %ZZ*case it is all SQL.  no Objectscript involved
as you see in this reply
 

The dirty hack is based on editing a copy of the class you want to query
as described in my other reply.
For a description of extended global reference syntax, see Extended Global References.
 

THANKS for your feedback !

A very dirty hack:  possible not really a recommendation

IF Source table is not mapped to ONS

1. copy class definition including <Storage> from ENS to ONS
2. in <STORAGE> you see

3. <DataLocation>^Sample.EmployeeD</DataLocation>
   <IdLocation>^Sample.EmployeeD</IdLocation>
   <IndexLocation>^Sample.EmployeeI</IndexLocation>
   <StreamLocation>^Sample.EmployeeS</StreamLocation> 

4. Now replace all  ^  by ^|"ENS"|  looks similar 
   For my example, it's ^|"SAMPLES"|

5. <DataLocation>^|"SAMPLES"|Sample.EmployeeD</DataLocation>
   <IdLocation>^"|SAMPLES"|Sample.EmployeeD</IdLocation>
   <IndexLocation>^|"SAMPLES"|Sample.EmployeeI</IndexLocation>
   <StreamLocation>^|"SAMPLES"|Sample.EmployeeS</StreamLocation> 

6. Extended Global references allow access to the other namespace. Nothing new, but risky

YES Sir!

• Process private globals live in IRISTEMP and are strictly bound to the process and its lifetime.
• The package with %ZZ.* makes sure it is visible from ALL namespaces
• At process start it is always empty
  • you fill it implicitely by CREATE at definiftion time
  • or if reused by 
    INSERT INTO %ZZ.resultset SELECT .......
  • this SELECT must be the same as in CREATE ...
    to  fit  column names
• Either CREATE once + INSERT later works
• Or DROP TABLE %ZZ..... and fresh CREATE ...
• It's a matter of taste, I tried both

Warning: 
Test from MGMT Portal (SQL) doesn't work as the process 
(and its ID) in background changes. The process stops and the content is gone.

IF your table must survive several processes, you need some 
other tricky solution based on the same basic idea of %ZZ*
combined with a manually tuned Class definition.

Hi @Evgeny Shvarov 
I created a mix from #1 and #2 without global mapping.
Temporary tables are excellent for this challenge 
as they drop at the end of the process. No clean-up needed !
It's kind of a Snapshot
 

A totally different approach, excluding JSON, pure SQL

1. create temporary table
2. Insert your result
3. consume it within the same process
4. eventually drop the table definition for a different query

I use the same query for my small code example

SELECT TOP 5 ssn, home_state,name from Sample.employee

USER>
USER>ZN "SAMPLES"

SAMPLES>:sql
SQL Command Line Shell
-----------------------------
[SQL]SAMPLES>>  << entering multiline statement
       1>>CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as
       2>>SELECT TOP 5 ssn, home_state,name from Sample.employee
       3>>go
       
17.    CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as
       SELECT TOP 5 ssn, home_state,name from Sample.employee
5 Rows Affected
[SQL]SAMPLES>>quit

SAMPLES>zn "USER"
USER>:sql
SQL Command Line Shell
-----------------------------
[SQL]USER>>SELECT * from %ZZ.resultset
18.     SELECT * from %ZZ.resultset

| SSN | Home_State | Name |
| -- | -- | -- |
| 217-28-9642 | NC | Emerson,Xavier O. |
| 226-71-5540 | CO | Malkovich,Bob V. |
| 396-77-7486 | UT | Joyce,Juanita V. |
| 558-18-5848 | IA | Ingleman,Josephine Q. |
| 943-90-5082 | TN | Page,Valery R. |

5 Rows(s) Affected

Now it's all in your hands
Eventually run DROP TABLE %ZZ.resultset
before next use with different columns.
Or just use different temp table for repeated use with other SELECT

Once upon a time, there was no JSON support!
But your challenge could be resolved by these steps:

1. ZN form original namespace (ONS) to execution namespace (ENS)
2. Execute query in ENS and get ResultSet 
3. While in ENS, let %Display print the Resultset into SPOOL
4. ZN to ONS.
5. Loop over the spooled ResultSet to get your data line by line

A simplified example:

USER>ZN "SAMPLES"
SAMPLES>set sql="SELECT top 5 name, ssn from sample.employee"
SAMPLES>set sta=##class(%SQL.Statement).%New()
SAMPLES>write sta.%Prepare(.sql)
1
SAMPLES>set res=sta.%Execute()
SAMPLES>kill ^SPOOL($j)
SAMPLES>open 2:$JOB
SAMPLES>use 2 do res.%Display()
SAMPLES>close 2
SAMPLES>ZN "USER"

USER>Zwrite ^|"SAMPLES"|SPOOL($JOB)
^|"SAMPLES"|SPOOL(13488,1)="Name"_$c(9)_"SSN"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,2)="Emerson,Xavier O."_$c(9)_"217-28-9642"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,3)="Malkovich,Bob V."_$c(9)_"226-71-5540"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,4)="Joyce,Juanita V."_$c(9)_"396-77-7486"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,5)="Ingleman,Josephine Q."_$c(9)_"558-18-5848"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,6)="Page,Valery R."_$c(9)_"943-90-5082"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,7)=$c(13,10)
^|"SAMPLES"|SPOOL(13488,8)="5 Rows(s) Affected"
^|"SAMPLES"|SPOOL(13488,2147483647)="{67640,77930{9{"
 
USER> 

The content here is:

Name    SSN
Emerson,Xavier O.       217-28-9642
Malkovich,Bob V.        226-71-5540
Joyce,Juanita V.        396-77-7486
Ingleman,Josephine Q.   558-18-5848
Page,Valery R.  943-90-5082
5 Rows(s) Affected
Now you can loop over your result as on any other Global
as you need

ClassMethods can be projected to SQL as Procedures.
That's what I  used in the only class (ISOS.cls) of this package
The package was written for IRIS but worked in Caché as well for ages..

In OEX there are 2 examples to create a persistent (or temporary) class from JSON
Launching a related query to get a ResultSet is just a standard exercise then.

• https://openexchange.intersystems.com/package/JSON2Persistent
• https://openexchange.intersystems.com/package/json2class 

so this acts in some way similar to #(variable + formula)# syntax
that we know from &html<..> or &js< ..> blocks for CSP

??

first you may try to run your query in your code for a few records 
to see by what format EFFDT is returned internally

As my 5* review in OEX is still blocked after fixing 
a minor typo in text I'lll publish it here.
-----------------------------------------------------------------------