[thelist] searching and JSON

[Nadeem Hosenbokus]

I'm curious now - what exactly is the application/site? How did you end up
with JSON data in your database without other columns to flag whatever
parameters you need to search by? Wouldn't the JSON in the table be just for
retrieval?

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 07:32
To: thelist at lists.evolt.org
Subject: Re: [thelist] searching and JSON

/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)

-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !