CODEWITHSUNDEEP
jsonmariadbjson-extract
Baton8546
Baton8546

Reputation: 1

Extracting value from a JSON array in a MariaDB table column

Would appreciate if you can help a mariaDB newbie with this.

I have a mariaDB database table called 'brand' with data as below.

Datatypes are VARCHAR and JSON.

name varchar(255) NOT NULL

sold longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid('sold'))
name sold
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":35000}]
Test4 [{"id":"0","name":"0","count":700},{"id":"1","name":"1","count":600}]

When I execute the query:

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

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

I get the below output

name cnt
Test [5000, 5000]
Test2 10000
Test3 15000
Test4 700

I was expecting output as below

name cnt
Test [5000, 5000]
Test2 [10000, 10000]
Test3 [15000, 35000]
Test4 [700, 600]

Can you please help me correct the error?

  1. I have searched the mariaDB documentation https://mariadb.com/kb/en/json_extract/ but not able to find a solution yet.
  2. I also looked at this post: How do I extract values from a JSON array in MariaDB or MySQL? and I am implementing same SQL...

Thank you!

Update: 25 Jan

Ticket submitted to JIRA mariadb and fixed here

Upvotes: 0

Views: 596

Answers (0)

Related Questions