Code example to build a %Library.DynamicObject or a %Library.DynamicArray from Resultset followed by converting to JSON

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

You could do as Robert said, or you could create an SQL gateway connection to the execution namespace and query it from the original namespace.

It's not a matter of ResultSet availability in another namespace; it's a matter of the data you have access to in one namespace and not in another. 

if you need the access to data that is available in namespace A to namespace B, there are many ways to make it work:

1. map the data for namespace B  via global mappings and run the query and work with the ResultSet in namespace B directly.
2. Parse the resultset in namespace A, put all the results into a global that is visible in namespace B via extended syntax Access the global in namespace B directly.
3. Put the results into a file on a disc or %Stream and work with them in namespace B.
4. any other way that is more suitable to your task. 

But reconstructing the ResultSet, which is just an object in memory suitable to access the data that is still in Namespace A, I wouldn't follow the idea.

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

From the description of your task, it will be easier to use the %ScrollableResultSet class.

Here is a small example:

try{
  n $namespace
  s $namespace="ENS"
  s rs=##class(%ScrollableResultSet).%New()
  d rs.Prepare("query in ENS")
  d rs.Execute()
  d rs.%Save()
  s rsId=rs.%Id()
  w "rsId=",rsId,!
  k rs

  s $namespace="ONS"
  
  s rs=##class(%ScrollableResultSet).%OpenId(rsId)
  while rs.Next() {
    w rs.Data("your data"),!
  }
}catch(ex){
}