Unable to Read JSON Data in MySQL

Question

When I'm reading JSON data -

Sample query:

SELECT *
FROM
  JSON_TABLE(
          CONCAT('[', '351615,12345678', ']'),
          "$[*]"
          COLUMNS(
              ids BIGINT(20) PATH "$"
              )
      ) AS tt

Error :

"Missing a comma or ']' after an array element." at position 2.

When I remove 00 in front of integer it is giving proper result.

SELECT *
FROM
  JSON_TABLE(
          CONCAT('[', '351615,12345678', ']'),
          "$[*]"
          COLUMNS(
              ids BIGINT(20) PATH "$"
              )
      ) AS tt

Output:

351615
12345678

Can any one suggest me what I'm missing?

Answered here: https://stackoverflow.com/questions/27361565/why-is-json-invalid-if-an-integer-begins-with-a-leading-zero — guido, Jan 22 '20 at 09:50

Sebastian Brosch · Accepted Answer · 2020-01-22T10:42:51.387

1

You can use JSON_ARRAY instead of concat a string to get a JSON array value:

-- single value
SELECT *
FROM JSON_TABLE(
  JSON_ARRAY('00351615'),
  "$[*]"
  COLUMNS(
    ids BIGINT(20) PATH "$"
  )
) AS tt

-- multiple values
SELECT *
FROM JSON_TABLE(
  JSON_ARRAY('00351615', '12345678'),
  "$[*]"
  COLUMNS(
    ids BIGINT(20) PATH "$"
  )
) AS tt

... or you escape the value with ":

-- single value
SELECT *
FROM JSON_TABLE(
  CONCAT('[', '"00351615"', ']'),
  "$[*]"
  COLUMNS(
    ids BIGINT(20) PATH "$"
  )
) AS tt

-- multiple values
SELECT *
FROM JSON_TABLE(
  CONCAT('[', '"00351615","12345678"', ']'),
  "$[*]"
  COLUMNS(
    ids BIGINT(20) PATH "$"
  )
) AS tt

You can also check if your JSON value is valid, using JSON_VALID:

SELECT JSON_VALID(CONCAT('[', '00351615', ']')) -- 0 (not valid)

demo on dbfiddle.uk

edited Jan 22 '20 at 10:42

answered Jan 22 '20 at 09:50

Sebastian Brosch

42,106
15
72
87

when I send multiple values my query is working but your answer is giving error I have modified my question please check – mohan111 Jan 22 '20 at 10:36
@mohan111 - added examples for multiple values. – Sebastian Brosch Jan 22 '20 at 10:43
while executing it is removing front "00" zeros .can you see once – mohan111 Jan 22 '20 at 10:45
if I pass values like this (00351615,12345678) then it giving error I Can't add ('00351615','12345678') . can you suggest me – mohan111 Jan 22 '20 at 10:48

Naveen Kumar · Answer 2 · 2020-01-22T11:10:19.930

1

Try below query, it is working:

SELECT * FROM JSON_TABLE('[{"x":"00351615","y":"2"}]',"$[*]" COLUMNS(xval VARCHAR(100) PATH "$.x",yval VARCHAR(100) PATH "$.y")) AS  tt;

Data type should be varchar instead of bigint as value with '00' prefix can not be stored in bigint.

edited Jan 22 '20 at 11:10

answered Jan 22 '20 at 09:52

Naveen Kumar

1,988
1
7
11

To add multiple values you will have to add column also. – Naveen Kumar Jan 22 '20 at 11:09
I have updated the answer with multiple values(x & y). – Naveen Kumar Jan 22 '20 at 11:10

Unable to Read JSON Data in MySQL

2 Answers2