Joining and filtering tables on a nested field (in a JSON array)

jimbob72

I have a server-side script that joins several tables on simple columns and then filters by various criteria. So far, so good. But one of my columns contains a JSON array and I need to be able to filter on a value in that array.

a) Is it possible to join two tables like this: [code]
LEFT JOIN
$sTable2
ON ($sTable2.column1['field'] = $sTable1.column1)
[/code]
b) How do I write the associated filter, e.g:
[code]{
$sWhere .= "(";
$sWhere .= $aColumns[1]['field']." = ".$mycriteria;
$sWhere .= ") AND ";
} [/code]

Any help much appreciated.

jimbob72

Anyone?

allan

> But one of my columns contains a JSON array and I need to be able to filter on a value in that array.

Does the SQL engine that you are using support being able to access JSON objects inside the query? I know that Postgres 9.2 added JSON support, but that was only as a data type - it can't (as far as I know) actually access the JSON properties. I'm not aware of any SQL databases which do operate like that - some of the No-SQL dbs do I think, but I'm much more of a Javascripter than SQL fella!

Unless your SQL engine does support this, then you would need to bring the data into the application space and do the filtering there - at which point you will loose the benefits of server-side processing. I'm afraid at that point you would need to consider altering the SQL schema.

Allan

jimbob72

Hmm. That's what I feared. Is there any way I can exclude a row in the output using the server side script? e.g.:
[code]
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i

allan

The problem with that is that you need to bring all of the records into the program space so you can discard the ones which don't met your filtering criteria, while having knowledge of what the full result set looks like.

For example, lets say you have 10 records per page. If you were to simply pull out 10 records from the db, you might discard 6 of them. You can't just return the 4 remaining rows to the client-side, since it is expecting 10 rows. So you'd need to do another pull from the server, where the same issue might occur again. And that is compounded by the fact that its not just the 10 rows on the current page you need to know about, you need to know how many rows are in the result set total, otherwise pagination will be buggy.

So assuming the database you are using can't read JSON properties, I think you have two choices:

1. Alter the schema to make the query you want to perform possible
2. Do everything in client-side processing or in the server-side application space.

Allan

jimbob72

Thanks Allan. I've bitten the bullet and created a separate aggregate table in place of the JSON value (because I can't tamper with the columns in the table that contains the JSON value as it's a system table). Now I can join the two original tables via the third aggregate table in the server-side script.

Means reworking a number of data connections but is probably the best long-term solution.

allan

Yup fair enough. That sounds like a nice solution. I wouldn't be surprised once day if Postgres folks do expand its JSON offerings to being able to work with JSON objects, but who know when it will happen.

Allan