[MDEV-30466] Extracting value from a JSON array using JSON_EXTRACT inconsistent among versions Created: 2023-01-25  Updated: 2023-01-25  Resolved: 2023-01-25

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.9.0, 10.10.2
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: AR Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

mac os monterey v12.1

Issue Links:
Duplicate
duplicates MDEV-30304 Json Range only affects first row of ... Closed

 Description   

Step1: Create table

CREATE TABLE IF NOT EXISTS brands (
name VARCHAR(255) NOT NULL,
sold longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
);

Step2: Insert values into table

INSERT INTO brands (name, sold)
VALUES
('test', '[

{"id":"0","name":"0","count":5000}

,

{"id":"1","name":"1","count":5000}

]'),
('test2', '[

{"id":"0","name":"0","count":10000}

,

{"id":"1","name":"1","count":10000}

]'),
('test3', '[

{"id":"0","name":"0","count":15000}

,

{"id":"1","name":"1","count":15000}

]'),
('test4', '[

{"id":"0","name":"0","count":700}

,

{"id":"1","name":"1","count":700}

]');

Step3: Extract values using query

SELECT name, JSON_EXTRACT(sold, '$[*].count') as cnt FROM brands;

Output is incorrect when running V10.9 and above:

name cnt
test [5000, 5000]
test 10000
test 15000
test 700

Output is correct when running V10.8 and below:

name cnt
test [5000, 5000]
test [10000, 10000]
test [15000, 15000]
test [700, 700]

Test fiddle here: https://dbfiddle.uk/0A5oD29V

 Comments   
Comment by Alice Sherepa [ 2023-01-25 ]

Thank you for the report!
This is fixed by b915b96f7224e04d4509f1e4 (MDEV-30304)

Generated at Thu Feb 08 10:16:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.