Basic Query

Basic Query 🔍

Ready to flex your SQL muscles on JSON data? You're in the right place! In this guide, we'll walk through everything from simple picks to complex filtering, sorting, and our super-smart Master-Detail output. Let's turn that messy JSON into pure gold! 💎✨

---

📄 Our Playground Data

Check out this structure! We've got some global info at the root and a list of cool team members to play with. ⬇️

{
  "Organization": "Tech Core Global",
  "TotalBudget": 50000,
  "Currency": "USD",
  "Members": [
    { "Id": 101, "FullName": "Fadhly Permata", "Role": "Lead Architect", "Department": "Core Engine", "Salary": 9500, "Active": true },
    { "Id": 102, "FullName": "Budi Santoso", "Role": "Senior Developer", "Department": "Core Engine", "Salary": 7500, "Active": true },
    { "Id": 103, "FullName": "Sari Wijaya", "Role": "UI Designer", "Department": "Frontend", "Salary": 6000, "Active": false },
    { "Id": 104, "FullName": "Andi Pratama", "Role": "DevOps Engineer", "Department": "Infrastructure", "Salary": 8000, "Active": true },
    { "Id": 105, "FullName": "Rina Gunawan", "Role": "QA Engineer", "Department": "Infrastructure", "Salary": 5500, "Active": true }
  ]
}

---

🚀 Query Examples (From Simple to Pro)

Here are 15 battle-tested scenarios supported by JQL.Net. Let's go! 🏃💨

1. The "Root Grabber"

Just need a single value from the top level? Easy peasy!

SELECT $.Organization, $.Currency FROM $

Output:

{
  "Organization": "Tech Core Global",
  "Currency": "USD"
}

---

2. Grab 'Em All!

Want to see every single member in the array? Say no more! 📦

SELECT * FROM $.Members

Output:

[
    { "Id": 101, "FullName": "Fadhly Permata", "Role": "Lead Architect", "Department": "Core Engine", "Salary": 9500, "Active": true },
    { "Id": 102, "FullName": "Budi Santoso", "Role": "Senior Developer", "Department": "Core Engine", "Salary": 7500, "Active": true },
    ...
  ]

---

3. The Makeover (Aliasing)

Rename your fields on the fly to make them look pretty! ✨

SELECT FullName AS Name, Role AS Position FROM $.Members

Output:

[
  { "Name": "Fadhly Permata", "Position": "Lead Architect" },
  { "Name": "Budi Santoso", "Position": "Senior Developer" },
  ...
]

---

4. The Salary Filter (WHERE)

Who's making the big bucks? 💸

SELECT FullName, Salary FROM $.Members WHERE Salary > 8000

Output:

[
  { "FullName": "Fadhly Permata", "Salary": 9500 },
  { "FullName": "Andi Pratama", "Salary": 8000 }
]

---

5. Double Trouble (AND)

Filtering with multiple conditions like a pro. 🕵️‍♂️

SELECT FullName FROM $.Members WHERE Department == 'Core Engine' AND Active == true

Output:

[
  { "FullName": "Fadhly Permata" },
  { "FullName": "Budi Santoso" }
]

---

6. Pick Your Choice (OR)

Need to find members with specific roles? JQL.Net handles it smoothly! 🤝

SELECT FullName, Role FROM $.Members WHERE Role == 'UI Designer' OR Role == 'QA Engineer'

Output:

[
  { "FullName": "Sari Wijaya", "Role": "UI Designer" },
  { "FullName": "Rina Gunawan", "Role": "QA Engineer" }
]

---

7. Order in the Court! (ASC)

Sort those names alphabetically. 🔡

SELECT FullName FROM $.Members ORDER BY FullName ASC

Output:

[
  { "FullName": "Andi Pratama" },
  { "FullName": "Budi Santoso" },
  { "FullName": "Fadhly Permata" },
  { "FullName": "Rina Gunawan" },
  { "FullName": "Sari Wijaya" }
]

---

8. Show Me the Money! (DESC)

Sort from highest salary to lowest. 📈

SELECT FullName, Salary FROM $.Members ORDER BY Salary DESC

Output:

[
  { "FullName": "Fadhly Permata", "Salary": 9500 },
  { "FullName": "Andi Pratama", "Salary": 8000 },
  { "FullName": "Budi Santoso", "Salary": 7500 },
  { "FullName": "Sari Wijaya", "Salary": 6000 },
  { "FullName": "Rina Gunawan", "Salary": 5500 }
]

---

10. Quick Math: The Count 🔢

How many members do we actually have?

SELECT COUNT(Id) AS TotalMember FROM $.Members

Output:

{ "TotalMember": 5 }

---

11. Quick Math: Sum & Average 🧮

Calculating the budget has never been this fun!

SELECT SUM(Salary) AS TotalPayout, AVG(Salary) AS AveragePay FROM $.Members

Output:

{ "TotalPayout": 36500, "AveragePay": 7300 }

---

12. The Ultimate Combo 🍔

Filtering and sorting working together in perfect harmony!

SELECT FullName AS Staff, Salary FROM $.Members WHERE Active == true ORDER BY Salary DESC

Output:

[
  { "Staff": "Fadhly Permata", "Salary": 9500 },
  { "Staff": "Andi Pratama", "Salary": 8000 },
  { "Staff": "Budi Santoso", "Salary": 7500 },
  { "Staff": "Rina Gunawan", "Salary": 5500 }
]

---

13. Smart Master-Detail (The "Magic" Feature) 🪄

Mix root data ($.) and array data, and JQL.Net will automatically group them for you!

SELECT $.Organization, FullName FROM $.Members

Output:

{
  "Organization": "Tech Core Global",
  "FullName": ["Fadhly Permata", "Budi Santoso", "Sari Wijaya", "Andi Pratama", "Rina Gunawan"]
}

---

14. Nested Detail with Aliases 🏷️

Group your array under a specific key for super clean outputs!

SELECT $.Organization, m AS MemberData FROM $.Members AS m

Output:

 {
    "Organization": "Tech Core Global",
    "MemberData": [
      { "Id": 101, "FullName": "Fadhly Permata", "Role": "Lead Architect", "Department": "Core Engine", "Salary": 9500, "Active": true },
      { "Id": 102, "FullName": "Budi Santoso", "Role": "Senior Developer", "Department": "Core Engine", "Salary": 7500, "Active": true },
      ...
    ]
  }

---

15. The "God Mode" Query 🏆

Master-Detail + Aliasing + Filtering + Sorting. The whole suite!

SELECT $.Organization, m.FullName AS Name, m.Salary FROM $.Members AS m WHERE m.Active == true ORDER BY m.Salary DESC

Output:

{
  "Organization": "Tech Core Global",
  "Name": ["Fadhly Permata", "Andi Pratama", "Budi Santoso", "Rina Gunawan"],
  "Salary": [9500, 8000, 7500, 5500]
}