Mysql query WHERE Json value = this

Question

I need to write a mysql query within a xml field. Joomla provides me the format and its relatively simple for most things. Except can I actually write a query that checks a json value within the attribs column.

Below is just my example, I know it won't work. But that's what I want to do.

SELECT * 
FROM com_content 
WHERE atrribs(form_show) = 1

also below is the json format that the attribs column is holding

{

"form_show":"1",
"form_avilable":"0",
"mycategory":"4",
"response":"Thank you!",
"forms":{

"fieldinstruction":["blah","blah"],
"fieldmanditory":["0","0"]

}

}

Note: Here is the format joomla explains. Taken from joomla docs

docs.joomla.org/SQL_form_field_type

<field 
    name="myfield" 
    type="sql" 
    default="10" 
    label="Select an article" 
    query="SELECT id, title FROM #__content" 
    key_field="id" 
    value_field="title" 
/>

You can also assemble or calculate fields in the SQL statement. For example, suppose you wanted to append the created date/time of each article to the article title in the list. Then you could use this SQL statement:

SELECT id, concat( title, ' (', created, ')') AS title 
FROM #__content

What I need to do is write a mysql query using the above format but it needs to get a value from within one column and its also a json.

Thanks to anyone in advance

Answer 1

You cannot, which is precisely the reason storing JSON strings in a DB should be approached with caution. That being said, none of the example columns you used are stored in JSON in Joomla's content table. Are those the actual values you were looking for?

Your only option is to parse the returned result set in PHP to do any further filtering. Depending on your end goal you might be able to insert PHP logic into your MVC path model or table class to handle any filtering. Are you writing a custom component or implementing a layout override?

Answer 2

What you can try is to query

$mystring = '%'.'"form_show":"1"' . '%';
$query->where($db->quoteName('attrib')   .' LIKE '. $db->quote($mystring) );

(fixed this)