How to Query Json Array objects using SQL query

I am using soapuiPro tool to test REST service. This is my requirement.

I have connected sopauiPro with MongoDB using "mongodb.jdbc.MongoDriver". I am querying mondodb collection from soapuiPro using SQL query.

While querying  Json Array objects using SQL query, the result set is not returning correct values.

In mondoDB using $elemMatch , I can check whether ContentType for requested locale "es_MX" exist or not.

db.Category.find({'_id' : 'PL101', "availableArticles" : {"$elemMatch": {"contentType":"MANUALS",  "locale" : "es_MX"}}})

I have tried the same logic using SQL query but the output values are wrong. 

select _id from Category 

where _id = 'PL101' 

AND availableArticles.locale = 'es_MX' 

AND availableArticles.contentType='MANUALS'

SQL query is returning result even though for _id='132281' there is ContentType = MANUALS and locale = 'es_MX'  does not exist in collection.

SQL output:

<Results>

    <ResultSet fetchSize="100">

        <Row rowNumber="1">

            <_ID>PL101</_ID>

            <ANCESTORS>[productcategory, PF1]</ANCESTORS>

            <ROLLEDUPCOUNT>17643</ROLLEDUPCOUNT>

            <LOCALIZEDNAMES>[{ "locale" : "lt_LT" , "name" : "iMac"}, { "locale" : "zh_CN" , "name" : "iMac"}, { "locale" : "ro_RO" , "name" : "iMac"}, { "locale" : "el_GR" , "name" : "iMac"}]</LOCALIZEDNAMES>

            <AVAILABLEARTICLES>[{ "locale" : "da_DK" , "contentType" : "SPECIFICATIONS" , "count" : 10},</AVAILABLEARTICLES>

        </Row>

    </ResultSet>

</Results>

Collection name : Category

{

    "_id" : "PL101",

  "ancestors" : [ 

        "productcategory", 

        "PF1"

    ],

availableArticles" : [ 

        {

            "locale" : "da_DK",

            "contentType" : "MANUALS",

            "count" : 3

        }, 

        {

            "locale" : "de_DE",

            "contentType" : "MANUALS",

            "count" : 21

        }, 

       {

            "locale" : "es_ES",

            "contentType" : "MANUALS",

            "count" : 22

        }, 

}

Now that I see this message, I understand why you were asking about $elemMatch for SQL in the other thread.

Of course, the problem here is that SQL does not have an equivalent construct since arrays are not a standard relational DB feature.

Since I don't know how soapuiPro maps SQL to MongoDB query language, I don't know if they can handle this syntax or have its equivalent.

You might have more success asking on SoapUI forums about this.