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.
-----------------------------------------------------------------------

list display failed at that point also text was scrambled
now it's working normal again

So it's clear.
Old_Class loads old defined properties in a local Object structure 
and writes only this structure back.

It has no idea of New properties and doesn't write it.
Just a guess:
$LB(...) in ^GlobalD of Old_class is shorter than for New_class
 So %Save() of Old_class is a data killer.
 

OK,
but prerequisit is, that both definitions are identical.
Especially in the Storage definition, where properties are mapped to globals

a selphy ??  😉

ATTENTION ! This made me nervous.

In essence, the two classes are sharing the same data / Index / stream globals.

This means there is also
Parameter MANAGEDEXTENT As INTEGER = 0;
Otherwise, it wouldn't compile