JSON Array Indexes in MySQL

JSON Array Indexes in MySQL

• 1.
  Copyright © 2019Oracle and/or its affiliates.1 JSON array indexes in MySQL Norvald H. Ryeng Software Development Senior Manager MySQL Optimizer Team October 2, 2019 New in 8.0.17
• 2.
  Copyright © 2019Oracle and/or its affiliates.2 Safe harbor statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation.
• 3.
  Copyright © 2019Oracle and/or its affiliates.3 Program agenda 1 What is it? 2 How do I use it? 3 Demo
• 4.
  Copyright © 2019Oracle and/or its affiliates.4 What is it?  Index of a JSON array A functional index over a JSON expression The expression evaluates to an array  Several index entries per row One index entry per array element General mechanism, currently used for JSON arrays  Used to speed up array lookups JSON_CONTAINS(…) JSON_OVERLAPS(…) MEMBER OF (…)
• 5.
  Copyright © 2019Oracle and/or its affiliates.5 Limitations  Can be part of a multi-column index Only one array expression per index Avoiding combinatorial explosion  Can flatten and index nested arrays CAST(json->'$.the_array[*][*]' AS UNSIGNED INT ARRAY)  Can't be PRIMARY  Can be UNIQUE
• 6.
  Copyright © 2019Oracle and/or its affiliates.6 How do I use it? CREATE TABLE lottery (data JSON); CREATE INDEX ticket_idx ON lottery ((CAST(data->'$.lottery_tickets' AS UNSIGNED INT ARRAY))); INSERT INTO lottery VALUES ('{"id":1, "name":"Alice", "lottery_tickets": [1]}'), ('{"id":2, "name":"Bob", "lottery_tickets": [2,22]}'), ('{"id":3, "name":"Carol", "lottery_tickets": [3,33,333]}'), ('{"id":4, "name":"Dan", "lottery_tickets": [4,44,444,4444]}'), ('{"id":5, "name":"Erin", "lottery_tickets": [5,55,555,5555,55555]}'); Cast to array is only available when creating a functional index Type of array element
• 7.
  Copyright © 2019Oracle and/or its affiliates.7 New in 8.0.17 How do I use it? Find all rows where the array contains a given value SELECT data FROM lottery WHERE JSON_CONTAINS(data->'$.lottery_tickets', CAST(22 AS JSON)); or SELECT data FROM lottery WHERE 22 MEMBER OF (data->'$.lottery_tickets'); Find the one winner
• 8.
  Copyright © 2019Oracle and/or its affiliates.8 How do I use it? Find all rows that contain one or more of the given values SELECT data FROM lottery WHERE JSON_OVERLAPS(data->'$.lottery_tickets', CAST('[22,44]' AS JSON)); Find everyone with at least one winning ticket New in 8.0.17
• 9.
  Copyright © 2019Oracle and/or its affiliates.9 Demo MySQL 8.0.17
• 13.
  Copyright © 2019Oracle and/or its affiliates.13 Feature descriptions and design details directly from the source https://mysqlserverteam.com/
• 14.
  Copyright © 2019Oracle and/or its affiliates.14 Thank you! Norvald H. Ryeng Software Development Senior Manager MySQL Optimizer Team