Search Smith

ColdFusion, SQL queries, and, of course, searching

Posts Tagged ‘JSON’

ColdFusion and Solr: Dealing with the results

Posted by David Faber on March 8, 2012

The Solr web service allows you to return results in either XML format or JSON (the format is XML by default; to return results in JSON format, add the parameter “wt=json” to your Solr query string). Suppose we run a query on the Solr collection myindex. There are three ways we can search this collection: (1) we could use the <cfsearch> tag; (2) we could use the Solr web service and return XML; or (3) we could use the Solr web service and return JSON. We’ve talked about <cfsearch> and its limitations before so I’ll limit this discussion to the second and third options.

XML:

<cfhttp url="http://localhost:8983/solr/myindex?q=*:*&fl=*,score" result="myresult" />

JSON:

<cfhttp url="http://localhost:8983/solr/myindex?q=*:*&fl=*,score&wt=json" result="myresult" />

In each case (XML or JSON), ColdFusion has powerful functions that help you manage the data without needing to parse it manually. Typically I create a friendly query that I can return to the front-end developer that he can use in place of a SQL query or <cfsearch> result.

<cfset search_results = queryNew("id,title,description,pubdate,journal_name,author_name,num_reads,score"
                               , "CF_SQL_INTEGER,CF_SQL_VARCHAR,CF_SQL_CLOB,CF_SQL_DATE,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_INTEGER,CF_SQL_DOUBLE")
/>
<!--- We're using our articles index mentioned in a previous post --->

XML:

<cfif isXML(myresult)>
    <cfset xml_result = XMLParse(myresult.fileContent) />
    <!--- continue processing --->
</cfif>

JSON:

<cfif isJSON(myresult)>
    <cfset json_result = deserializeJSON(myresult.fileContent) />
    <!--- continue processing --->
</cfif>

In either case we will iterate over our results structure and put the results in our friendly query.

JSON:
The JSON structure is particularly friendly for our purposes. First, let’s get the number of results and the maximum score:

<cfset result_cnt = json_result.response.numFound />
<cfset max_score = json_result.response.maxScore />

These fields aren’t absolutely necessary, of course. In particular, maxScore seems superfluous but it might be useful if you’re converting from Verity to Solr and need scores in a % format. The Verity search engine scores results between 0 and 1, which is easily converted to a percentage; while Solr has no maximum score. So to get a Verity-style score, simply divide each result’s score by maxScore.

Now we simply loop over the docs array of the response:

<cfloop array="#json_result.response.docs#" index="doc">
    <cfif structKeyExists(doc, "id") AND structKeyExists(doc, "title")>
        <!--- Both id and title are absolutely required --->
        <cfset queryAddRow(search_results) />
        <cfset querySetCell(search_results, "id", doc[id]) />
        <cfset querySetCell(search_results, "title", doc[title]) />
        <cfset querySetCell(search_results, "score", doc[score] / max_score) />
        <cfif structKeyExists(doc, "description")>
            <cfset querySetCell(search_results, "description", doc[description]) />
        </cfif>
        <cfif structKeyExists(doc, "pub_date")>
            <cfset querySetCell(search_results, "pub_date", doc[pub_date]) />
        </cfif>
        <cfif structKeyExists(doc, "journal_name")>
            <cfset querySetCell(search_results, "journal_name", doc[journal_name]) />
        </cfif>
        <cfif structKeyExists(doc, "author_name")>
            <cfset querySetCell(search_results, "author_name", doc[author_name]) />
        </cfif>
        <cfif structKeyExists(doc, "read_cnt")>
            <cfset querySetCell(search_results, "read_cnt", doc[read_cnt]) />
        </cfif>
    </cfif>
</cfloop>

Dealing with XML results is a bit more complicated so I will discuss that in a subsequent post or in an update to this one. Suffice it to say that the approach will be very similar – we will iterate over the results and store them in a friendly query.

Posted in ColdFusion, Solr | Tagged: , , , , , , , | 7 Comments »

ColdFusion 9: Preserving sort order with structs

Posted by David Faber on January 28, 2012

In a previous post I talked about using structs to avoid using CFQUERY inside a query loop. Structs are also useful if you need to return your data in JSON format (of course, ColdFusion can return query objects in JSON format as well, but I think this format can be difficult to use if you’re using something other than ColdFusion to read it back in). In this vein, it would be helpful if structs retained sort order. This helpful blog post shows how to do just that.

Posted in ColdFusion | Tagged: , , , | Leave a Comment »