IP Library › Granted Patent US 12,373,432
Granted Patent B2
US 12,373,432 · App. 18/064,214 · Granted Jul 29, 2025

Unnesting of JSON arrays

Inventors: Stefano Belloni (Mannheim, DE); Christian Bensberg (Heidelberg, DE); Daniel Ritter (Heidelberg, DE)
Assignee: SAP SE
G06F16/24549G06F16/213G06F16/24537G06F16/24542G06F16/93
View Patent ↗
Loading inventors, assignments & file history…
Monitor This Case
Get email alerts when status or documents change.
Order Certified Copies
Most orders are placed with the USPTO same day — all within 24 business hours.
Order via The Patent Place →
Pre-filled with this patent's details
Quick Facts
Patent No.
US 12,373,432
App. No.
18/064,214
Filed
Dec 9, 2022
Granted
Jul 29, 2025
Kind
B2
Art Unit
2154
USPC
707/718
Abstract

A method may include receiving a query including at least one unnest operation to unnest a plurality of elements from one or more JavaScript Object Notation (JSON) arrays. The at least one unnest operation may unnest the plurality of elements by generating a table in which each row of the table is populated by one of the plurality of elements. An execution plan may be generated to include a pre-filter operation to filter, prior to the at least one unnest operation, the plurality of elements included in the one or more JSON arrays. For example, the pre-filter operation may be performed by iterating through the elements from the one or more JSON arrays to identify one or more elements satisfying a predicate included in a where clause of the query. The query may be executed in accordance with the execution plan. Related systems and computer program products are also provided.

Claims (31)

1. A system, comprising:

at least one data processor; and

at least one memory storing instructions which, when executed by the at least one data processor, cause operations comprising:

receiving a query including at least one unnest operation to unnest a plurality of elements from one or more JavaScript Object Notation (JSON) arrays;

generating an execution plan to include a pre-filter operation to filter the plurality of elements included in the one or more JSON arrays before the at least one unnest operation to unnest the plurality of element from one or more JSON arrays; and

executing the query in accordance with the execution plan.

2. The system of claim 1 , wherein the at least one unnest operation unnests the plurality of elements by at least generating a table in which each row of the table is populated by an element from the plurality of elements.

3. The system of claim 1 , wherein each element of the plurality of elements is associated with a path that includes one or more other elements of the plurality of elements that are traversed in order to reach the element.

4. The system of claim 1 , wherein the at least one unnest operation includes a first unnest operation performed on a first array and a second unnest operation performed on a second array.

5. The system of claim 4 , wherein the first unnest operation and the second unnest operation form a sequential unnest operation based at least on the first unnest operation and the second unnest operation sharing a common sub-path with a common root element, and wherein the sequential unnest operation includes concatenating a first result of the first unnest operation and a second result of the second unnest operation.

6. The system of claim 4 , wherein the first unnest operation and the second unnest operation form a Cartesian unnest operation based at least on the first unnest operation and the second unnest operation not sharing a common root element, and wherein the Cartesian unnest operation includes generating a Cartesian product of a first result of the first unnest operation and a second result of the second unnest operation.

7. The system of claim 1 , wherein the at least one unnest operation is performed without indexing the plurality of elements from the one or more JSON arrays.

8. The system of claim 1 , wherein the executing of the query includes performing the pre-filter operation by at least iterating through the plurality of elements from the one or more JSON arrays to identify one or more elements satisfying a predicate included in a where clause of the query.

9. The system of claim 1 , wherein the pre-filter operation is performed to reduce a quantity of elements to be unnested when performing the at least one unnest operation.

10. The system of claim 1 , wherein the plurality of data includes custom data and/or existing data associated with one or more document stores.

11. The system of claim 1 , wherein the query is executed at a document store storing one or more JSON documents including the one or more JSON arrays.

12. The system of claim 11 , wherein the document store is a JSON document store and/or a relational database with a JSON extension.

13. A computer-implemented method, comprising:

receiving a query including at least one unnest operation to unnest a plurality of elements from one or more JavaScript Object Notation (JSON) arrays;

generating an execution plan to include a pre-filter operation to filter the plurality of elements included in the one or more JSON arrays before the at least one unnest operation to unnest the plurality of element from one or more JSON arrays; and

executing the query in accordance with the execution plan.

14. The method of claim 13 , wherein the at least one unnest operation unnests the plurality of elements by at least generating a table in which each row of the table is populated by an element from the plurality of elements, and wherein each element of the plurality of elements is associated with a path that includes one or more other elements of the plurality of elements that are traversed in order to reach the element.

15. The method of claim 13 , wherein the at least one unnest operation includes a first unnest operation performed on a first array and a second unnest operation performed on a second array.

16. The method of claim 15 , wherein the first unnest operation and the second unnest operation form a sequential unnest operation based at least on the first unnest operation and the second unnest operation sharing a common sub-path with a common root element, and wherein the sequential unnest operation includes concatenating a first result of the first unnest operation and a second result of the second unnest operation.

17. The method of claim 15 , wherein the first unnest operation and the second unnest operation form a Cartesian unnest operation based at least on the first unnest operation and the second unnest operation not sharing a common root element, and wherein the Cartesian unnest operation includes generating a Cartesian product of a first result of the first unnest operation and a second result of the second unnest operation.

18. The method of claim 13 , wherein the at least one unnest operation is performed without indexing the plurality of elements from the one or more JSON arrays.

19. The method of claim 13 , wherein the executing of the query includes performing the pre-filter operation by at least iterating through the plurality of elements from the one or more JSON arrays to identify one or more elements satisfying a predicate included in a where clause of the query, and wherein the pre-filter operation is performed to reduce a quantity of elements to be unnested when performing the at least one unnest operation.

20. A non-transitory computer readable medium storing instructions, which when executed by at least one data processor, result in operations comprising:

receiving a query including at least one unnest operation to unnest a plurality of elements from one or more JavaScript Object Notation (JSON) arrays;

generating an execution plan to include a pre-filter operation to filter the plurality of elements included in the one or more JSON arrays before the at least one unnest operation to unnest the plurality of element from one or more JSON arrays; and

executing the query in accordance with the execution plan.

Assignments (1)
ASSIGNMENT OF ASSIGNOR'S INTEREST Recorded Dec 9, 2022
From: BELLONI, STEFANO; BENSBERG, CHRISTIAN; RITTER, DANIEL
To: SAP SE
Reel/Frame 062047/0189 →
Continuity (2)
Provisional Application 63350321 · Jun 8, 2022
Related Publication 20230401208A1 · Dec 14, 2023
References Cited (27)
US 9589019B2 · Clifford et al. · 2017 [cited by applicant]
US 11416465B1 · Anwar et al. · 2022 [cited by applicant]
US 20130124467A1 · Naidu et al. · 2013 [cited by applicant]
US 20130166568A1 · Binkert et al. · 2013 [cited by applicant]
US 20160034478A1 · Hernandez-Sherrington et al. · 2016 [cited by applicant]
US 20170300517A1 · Amirsoleymani et al. · 2017 [cited by applicant]
US 20170308555A1 · Hirzel · 2017 [cited by examiner]
US 20210173621A1 · Fender · 2021 [cited by examiner]
Botoeva, Elena. Ontology-based Data Access—Beyond Relational Sources. Aug. 2019. Intelligenza Artificiale, vol. 13. pp. 21-36. [cited by examiner]
Abiteboul. S. et al., “Research Directions for Principles of Data Management,” Dagstuhl Perspectives Workshop 16151, Dagstuhl Afanifestos 7, 1 (2018). 1-29. [cited by applicant]
Bray, T. et al., “The Javascript Object Notation (JSON) Data Interchange Format,” (2014). [cited by applicant]
Chen, Y. et al., “A Study of SQL-on-Hadoop Systems,” In BPOE LNCS, vol. 8807, Springer, 154-166. [cited by applicant]
Cole, R.L. et al., “The mixed workload CH-benCHmark,” Proceedings of the Fourth International Workshop on Testing Database Systems. 2011. [cited by applicant]
Cooper, B.F. et al., “Benchmarking Cloud Serving Systems with YCSB,” In SoCC. ACM, 143-154. [cited by applicant]
Deep, S. et al. “DIAMetrics: Benchmarking Query Engines at Scale.” Proceedings of the VLDB Endowment 13.12. [cited by applicant]
Difallah, D.E. et al., “OLTP-Bench: An Extensible Testbed for Benchmarking Relational Databases,” Proc. VLDB Endow 7, 4 (2013), 277-288. [cited by applicant]
Erling, O. et al., “The LDBC Social Network Benchmark: Interactive Workload,” In SIGMOD. ACM. 619-630. [cited by applicant]
Gray, J. [Ed.] “Database and Transaction Processing Performance Handbook,” In The Benchmark Handbook for Database and Transaction Systems (2nd Edition). Morgan Kaufmann. [cited by applicant]
Ingo, H. et al., “Automated System Performance Testing at MongoDB,” In DBTest@SIGMOD. 3:1-3:6. [cited by applicant]
Jahangiri, S. “Wisconsin Benchmark Data Generator: To JSON and Beyond,” In SIGMOD. ACM, 2887-2889. [cited by applicant]
Kamsky, A. “Adapting TPC-C Benchmark to Measure Performance of Multi-Document Transactions in MongoDB,” Proc. VLDB Endow. 12, 12 (2019). 2254-2262. [cited by applicant]
Read, A.G. “DeWitt clauses: Can we protect purchasers without hurting Microsoft,” Rev. Litig. 25 (2006). 387-421. [cited by applicant]
Rigger, M et al., “Testing Database Engines via Pivoted Query Synthesis,” (2020), 667-682. [cited by applicant]
Ritter, D. et al., “Bench-marking integration pattern implementations,” In DEBS. ACM, 125-136. [cited by applicant]
Seltenreich, A. et al., “SQLSmith,” 2020. (Available at https://github.com/anse1/sqlsmith). [cited by applicant]
Vogelsgesang, A. et al., “Get Real: How Benchmarks Fail to Represent the Real World,” Proceedings of the Workshop on Testing Database Systems. 2018. [cited by applicant]
Zhong, R. et al., “SQUIRREL: Testing Database Management Systems with Language Validity and Coverage Feedback,” CCS'20: Proceedings of the 2020 ACM SIGSAC Conference on Computer and Communications Security. 2020. [cited by applicant]