Extract information from JSON

Hello everyone.
I came across strange behavior.
I have next json:

{ 
   "hr": { 
       "employees": [
           { "id": 3, "name": "Bob Smith",   "title": null }, 
           { "id": 4, "name": "Susan Smith", "title": "Dev Mgr" },
           { "id": 6, "name": "Jane Smith",  "title": "Software Eng 2"}
       ]
   }
} 

I want to extract id. But there is a unclear procces to extract value to me.
I found that next query extract id from input file to output file:
partiql —query=
SELECT k.id
from
(SELECT hr.employees from input_data) as e,
e.employees as k
-i q1.json -o res_q1.output

I received next:

<<
  {
    'id': 3
  },
  {
    'id': 4
  },
  {
    'id': 6
  }
>>

Question: why is the process so unclear?

Here is another query that extracts the employees ids by using a correlated join:

SELECT 
   e.id 
FROM 
   input_data i, 
   @i.hr.employees e

The @ on the join is for lexical scoping, i.e. the id i is resolved using with bindings from the FROM clause then global environment, i.e. catalog

Important to note that the CLI binds each input value as an element of input_data and that’s the query global environment. For the CLI each value in the input file is a row in the input_data table.

A JSON representation of that global environment is:

{
    "input_data": [{ 
        "hr": { 
           "employees": [
               { "id": 3, "name": "Bob Smith",   "title": null }, 
               { "id": 4, "name": "Susan Smith", "title": "Dev Mgr" },
               { "id": 6, "name": "Jane Smith",  "title": "Software Eng 2"}
           ]
       } 
    }]
}

In an application you’d have more control over how to specify the global environment. For example if you set your JSON file as the global environment you’d have a hr table and your query would be

SELECT e.id FROM hr.employees e
1 Like

Thank you!
And now I have another question: where can i find out about the @ operator?

At the moment the best place to learn more about the @ operator, or any other PartiQL feature, is the specification. Section 10 has information about scoping rules which includes the @ operator

1 Like