I have a table with a column named "data", it stores json data in form of a string like,

id , data

1 {'field1': '123432', 'field2': 'TEST', 'field3': '', 'field4': 'JAMES'}

2 {'field1': '123412', 'field2': 'TEST 2', 'field3': 'TEST', 'field4': 'TESTER'}

what is the best way to select all rows where "field3" of column "data" is not empty?

TEST QUERY

CREATE TABLE my_test(id INT,data TextField );

insert into my_test values (1,"{'field1': '123432', 'field2':'TEST'}"), (2,"{'field1': '322345', 'field2':'TEST 2'}");

Thank you.

CodePudding user response：

If your data are correct json format maybe something like this can help:

select JSON_UNQUOTE(JSON_EXTRACT(data, '$.field3'))
from test_tbl
where  JSON_UNQUOTE(JSON_EXTRACT(data, '$.field3')) !='' ;





CREATE TABLE test_tbl(
id INT,
data json  );

insert into test_tbl values
(1,'{"field1": "123432", "field2":"TEST", "field3": "", "field4": "JAMES"}'),
(2,'{"field1": "123432", "field2":"TEST", "field3": "TEST", "field4": "JAMES"}');

Result:

JSON_UNQUOTE(JSON_EXTRACT(data, '$.field3'))
TEST

Demo

CodePudding user response：

The way your data being stored does appear to look like JSON but unfortunately it's not a valid one. The single-quote should be double-quote instead. Depending on your MySQL version, you can verify whether the JSON data is valid or not by using JSON_VALID function:

SELECT *,
       JSON_VALID(data) AS check_if_json_data_is_valid
FROM my_test;

This is the result you'll get (with your exact data sample)

As you can see, check_if_json_data_is_valid returns 0(zero) means that it's not valid. Now, since your data is already a JSON format (despite the quotes), one simple solution to this is by replacing the single-quotes with double-quotes, hence:

SELECT *,
       REPLACE(data,'\'','"') AS replaced_quotes,
       JSON_VALID(REPLACE(data,'\'','"')) AS check_if_json_data_is_valid
FROM my_test;

This will return the following result:

With single-quotes being replaced by double-quotes, check_if_json_data_is_valid is now returning 1, so it's now a valid JSON value. What you can do next is taking the REPLACE(data,'\'','"') and use it for your operation:

SELECT *,
       JSON_UNQUOTE(JSON_EXTRACT(REPLACE(data,'\'','"'), '$.field3')) AS field3
      FROM my_test
HAVING field3 != ''

Demo fiddle

As for the INSERT query, either you're doing it manually or generating it through code, make sure you change the single-quotes to double-quotes so that you won't need to do REPLACE() on data column anymore. And make sure to check your JSON value with JSON_VALID too.