[thelist] searching and JSON

[Bob Meetin]

/Or, [select ColumnA from Table where ColumnA like '%{"Foo":"Bar"}%';] to match all records that have the specific pair value/

Okay so I'm doing a literal match on a string of text; that's it? Wow.

And to what Renoir said, I was thinking about creating a field and storing it there  as well to make the future simpler and quicker.

Along those lines, with a personal project I'm creating a set of similarly formatted pages with data coming from a variety of different source tables/fields, quite a few queries to gather and format it all.  Rather than build it as the viewer selects the page I decided to write the queries to build it in advance and store the result in a single field. I can run the script on an as needed basis.

The tips on JSON have helped much as well.


On 09/12/2012 06:14 PM, Nadeem Hosenbokus wrote:
> Hi,
>
> So if the JSON data looks something like this:
> {"Foo":"Bar"},{"Foo2":"Bar2"},{"Foo3":"Bar3"} in a column called
> 'Table.ColumnA'
>
> Then a query like this should work: [select ColumnA from Table where ColumnA
> like '%{"Foo":%';] if you want all the records that contain the "Foo"
> parameter.
>
> Or, [select ColumnA from Table where ColumnA like '%:"Bar"}%';] to match all
> the records that have a "Bar" value
>
> Or, [select ColumnA from Table where ColumnA like '%{"Foo":"Bar"}%';] to
> match all records that have the specific pair value
>
> Using the colon and curly brackets in the like condition can help you get
> the data you want.
>
> Hope this helps.
>
> Nadeem Hosenbokus
> (230) 766 9169
> www.nadeemh.com
>
>
>
> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Bob Meetin
> Sent: 13 September 2012 02:28
> To: thelist at lists.evolt.org
> Subject: [thelist] searching and JSON
>
> So you're working with a table that has parameterized data stored in a JSON
> formatted field.  How do you create a mysql query that matches against only
> a single element/parameter in the field without matching the other stored
> elements?
>
> -Bob


-- 
Bob Meetin
dotted i - http://www.dottedi.biz
Web Development / Joomla CMS Integration Specialist
LinkedIn: http://www.linkedin.com/in/bobmeetin
303-926-0167 (home/business)