Join error Type mismatch: expected value of type object, but got the value of type array

Analytics
1

Hi
I try to execute query like

SELECT col_doc_pr.data.en.alias
FROM `deploy` col_doc
JOIN `deploy` col_doc_pr ON col_doc_pr.id = col_doc.data.en.provider.id
 WHERE col_doc._type="game" AND col_doc._scope="bread"

But error occurs
“code”: 23023,
“msg”: “Type mismatch: expected value of type object, but got the value of type array (in line 1, at column 23)”,

But if execute like

SELECT col_doc_pr.data
FROM `deploy` col_doc
JOIN `deploy` col_doc_pr ON col_doc_pr.id = col_doc.data.en.provider.id
 WHERE col_doc._type="game" AND col_doc._scope="bread"

Success result

[
  {
    "data": {
      "en": {
        "alias": "tent",
        "allowed_countries": [],

This is a problem with join document only

How can I select .data.en data in analytic query?

2

Try this

SELECT 
col_doc_pr.data.`en`.`alias`
3

Unfortunately unchanged.
Displays only the first level of nesting col_doc_pr.data
If i try
col_doc_pr.data.en
“code”: 23023,
“msg”: “Type mismatch: expected value of type object, but got the value of type array (in line 1, at column 25)”,

4

Out of my expertise. cc @dmitry.lychagin.

5

Hi @Lenty_Me,

It looks like there might be some array data either in ‘data’ or ‘en’ fields. Could you double check?

SELECT DISTINCT typename(col_doc.data) FROM deploy col_doc

SELECT DISTINCT typename(col_doc.data.en) FROM deploy col_doc

If there’s an array data there then try modifying your SELECT clause as follows to avoid the error:
SELECT toobject(col_doc.data).en.alias FROM ...
or
SELECT toobject(col_doc.data.en).alias FROM ...

6

SELECT DISTINCT typename(col_doc_pr.data)
return

{
“$1”: “object”
}
col_doc work correctly but col_doc_pr from join

SELECT  DISTINCT typename(col_doc_pr.data.en) 
FROM `deploy` col_doc
JOIN `deploy` col_doc_pr ON col_doc_pr.id = col_doc.data.en.provider.id
 WHERE col_doc._type="game" AND col_doc._scope="bread"

"code": 23023,
** “msg”: “Type mismatch: expected value of type object, but got the value of type array (in line 1, at column 42)”,**

Only works with such a construction

select toobject(toobject(col_doc_pr.data).en).alias

If I try to execute SELECT toobject(col_doc_pr.data).en.alias
Error : "Type mismatch: expected value of type object, but got the value of type string (in line 1, at column 37)

Could it be a database bug?

7

@Lenty_Me ,

Which Couchbase Sever version you using?

8

Also, do you have any secondary indexes created in Analytics on ‘deploy’ dataset?

9

It would be helpful if you could also share sample documents that reproduce the problem.

10

For example:

{
  "_scope": "bread",
  "_type": "game",
  "account_id": "c5002e5d-9e1c-4051-9c9c-eaab4c99f1ce",
  "comment": "- Salsa baccarat  4\n- Sakura ",
  "data": {
    "en": {
      "alias": "asgardian-stones-netent",
      "allow_freespins": true,
      "allowed_countries": [],
      "bonus_no_play": false,
      "categories": [
        {
          "alias": "slots",
          "id": "7371929f-fa09-4239-873f-99bdc24034e5",
          "title": "Slots"
        }
      ],
      "codes": {
        "desktop": "netent:asgardianstones_not_mobile_sw",
        "external_desktop": "",
        "external_mobile": "",
        "mobile": "netent:asgardianstones_not_mobile_sw"
      },
      "data": {
        "bonus_no_play": false,
        "payout": 96.31,
        "rollover_allowed": 100
      },
      "demo": true,
      "description": "",
      "game_id": 23783,
      "height": "",
      "images": {
        "bg": [
          "https://cdn.softswiss.net/b/netent/asgardianstones_not_mobile_sw.jpg"
        ],
        "image": [
          "https://cdn.softswiss.net/i/s3/netent/asgardianstones_not_mobile_sw.png"
        ],
        "imagex2": [],
        "promo": []
      },
      "jackpot": false,
      "jackpotCode": "",
      "name": "Asgardian Stones",
      "new": false,
      "parent_id": "",
      "provider": {
        "alias": "netent",
        "id": "a46dcd5d-60b6-4dad-b853-e35481bb8202",
        "name": "Netent",
        "provider_id": 2880
      },
      "provider_view": true,
      "ratio": "",
      "restricted_countries": [
        {
          "code": "AF",
          "id": "cc4afa07-85cc-499e-b242-8098a688019f",
          "name": "Afghanistan"
        },
        {
          "code": "AL",
          "id": "01548a42-34b9-4de6-ac55-4e19632d3f54",
          "name": "Albania"
        },
        {
          "code": "AO",
          "id": "34b560a6-6e48-433d-ba9f-fb9393fec7c7",
          "name": "Angola"
        },
        {
          "code": "AU",
          "id": "3d6bde44-5bb3-417d-a7df-16492fe1f561",
          "name": "Australia"
        },
        {
          "code": "BE",
          "id": "80316424-ec2d-4a9b-b1eb-1fd68a42f5d7",
          "name": "Belgium"
        },
        {
          "code": "BG",
          "id": "c878aa6a-d116-497f-a181-6630cf2850ed",
          "name": "Bulgaria"
        },
        {
          "code": "CH",
          "id": "b9d5076e-97ab-4080-ae49-a9abf88e94b7",
          "name": "Switzerland"
        },
        {
          "code": "CZ",
          "id": "3a417bee-f7e2-4b31-b28f-a90ea5d6d5ef",
          "name": "Czech Republic"
        },
        {
          "code": "DK",
          "id": "cc666a3a-2f64-4b45-8ddb-588bebacb64b",
          "name": "Denmark"
        },
        {
          "code": "DZ",
          "id": "6f0e59b1-2a09-4373-8be8-35b390045a85",
          "name": "Algeria"
        },
        {
          "code": "EC",
          "id": "4840aa3b-2288-4d5a-9499-d975d3209ffc",
          "name": "Ecuador"
        },
        {
          "code": "EE",
          "id": "3c2d65b8-70b3-4292-b7bf-17fa42ab6250",
          "name": "Estonia"
        },
        {
          "code": "ES",
          "id": "684b87b3-bbce-434b-8517-617a4b4503a3",
          "name": "Spain"
        },
        {
          "code": "FR",
          "id": "7da640d6-f219-4d65-9fae-aedee6bb3a79",
          "name": "France"
        },
        {
          "code": "GB",
          "id": "83754448-ba9a-494f-9843-be1a616d4b86",
          "name": "United Kingdom"
        },
        {
          "code": "GY",
          "id": "4bb290e4-655b-43a7-8220-276d064856c8",
          "name": "Guyana"
        },
        {
          "code": "HK",
          "id": "19de1f8a-2a70-46c4-ba45-6df4aaad8caf",
          "name": "Hong Kong"
        },
        {
          "code": "ID",
          "id": "a89ca69b-eefc-4c2f-a980-03f73e4bfa37",
          "name": "Indonesia"
        },
        {
          "code": "IL",
          "id": "e616cde6-48ca-4fb3-a0cb-93586d1d8cac",
          "name": "Israel"
        },
        {
          "code": "IQ",
          "id": "6ccd7e1a-d930-4038-8f98-8f4947a3229a",
          "name": "Iraq"
        },
        {
          "code": "IR",
          "id": "9d7efe9b-4dc1-49fc-bb84-7d53b92577a6",
          "name": "Iran, Islamic Republic of"
        },
        {
          "code": "KH",
          "id": "8a2ce537-72fd-47bb-abf1-599e7baf410a",
          "name": "Cambodia"
        },
        {
          "code": "KP",
          "id": "db2843bc-dbb5-4ae9-9031-cd1fa8704d8b",
          "name": "Korea, Democratic People's Republic of"
        },
        {
          "code": "KR",
          "id": "aea0f0ff-6a52-4fa9-b17c-762007f9634a",
          "name": "Korea"
        },
        {
          "code": "KW",
          "id": "3d6ccf01-43f7-46a7-9bfb-c160707ca74a",
          "name": "Kuwait"
        },
        {
          "code": "LA",
          "id": "e854230e-3574-483e-a780-a87de4d9cce7",
          "name": "Laos"
        },
        {
          "code": "LT",
          "id": "21503527-ddde-4644-a187-8453b6cd7231",
          "name": "Lithuania"
        },
        {
          "code": "LV",
          "id": "fe23e445-1506-4875-95f1-e879a49ce3e7",
          "name": "Latvia"
        },
        {
          "code": "MM",
          "id": "29967dc6-529a-4e4f-bb61-e1848fc0c6bd",
          "name": "Myanmar"
        },
        {
          "code": "MX",
          "id": "6fe5eaa7-9b23-444e-b2c1-7e95991bc7b4",
          "name": "Mexico"
        },
        {
          "code": "NA",
          "id": "2610dca4-04dc-4e73-914f-ed9601774f16",
          "name": "Namibia"
        },
        {
          "code": "NI",
          "id": "c05ddba4-d2d2-4234-9271-f48c5082c1db",
          "name": "Nicaragua"
        },
        {
          "code": "PA",
          "id": "e4992ac3-94f8-47c9-9b0c-4d35b4976af8",
          "name": "Panama"
        },
        {
          "code": "PG",
          "id": "806dd3e5-24be-4ccd-bf58-f8b38b180c7a",
          "name": "Papua New Guinea"
        },
        {
          "code": "PH",
          "id": "178bdf7a-f9cf-4d30-9497-bbb1683c429f",
          "name": "Philippines"
        },
        {
          "code": "PK",
          "id": "c1177579-b4ae-4ac4-a78c-d33e0c75b6bc",
          "name": "Pakistan"
        },
        {
          "code": "PT",
          "id": "9da68cb1-1970-42e0-8f47-1099a0c8bffe",
          "name": "Portugal"
        },
        {
          "code": "RO",
          "id": "bb9daf31-eb37-4c7f-94e9-c7cbf2a9232e",
          "name": "Romania"
        },
        {
          "code": "RS",
          "id": "68a1ae50-dc73-46e4-80e7-378b0cfb397d",
          "name": "Serbia"
        },
        {
          "code": "SD",
          "id": "d2ce5fb4-b639-4162-bd35-ca632d7fb1e8",
          "name": "Sudan"
        },
        {
          "code": "SE",
          "id": "1a3c481c-2bf5-40dd-965b-dcb3714a5759",
          "name": "Sweden"
        },
        {
          "code": "SG",
          "id": "66d5657a-8adb-4dd2-b33d-40441345477d",
          "name": "Singapore"
        },
        {
          "code": "SY",
          "id": "a429f555-d84a-4d32-855c-2d58ee833218",
          "name": "Syria"
        },
        {
          "code": "TW",
          "id": "ef11dd25-7f76-4e51-926b-acd28f2edf3d",
          "name": "Taiwan"
        },
        {
          "code": "UG",
          "id": "f1b246ce-108b-4c53-a4aa-3e9b80b5d0c2",
          "name": "Uganda"
        },
        {
          "code": "US",
          "id": "13bfa166-674e-4071-ae43-761fa191ef1a",
          "name": "United States"
        },
        {
          "code": "YE",
          "id": "b893f22c-ec3e-4791-b1ca-ae93941b289f",
          "name": "Yemen"
        },
        {
          "code": "ZW",
          "id": "242e4b5a-d317-47d5-be5f-b83bc1274b8c",
          "name": "Zimbabwe"
        },
        {
          "code": "IT",
          "id": "8cad9b1b-5082-405f-a3f4-fa3b8e82a437",
          "name": "Italy"
        }
      ],
      "seo": {
        "description": "Asgardian Stones slot machine from Netent. Play Demo or Real Money in Asgardian Stones at RollingSlots Casino",
        "title": "Play Asgardian Stones slot | Get up to €500 + 100 FS Welcome Bonus | RollingSlots"
      },
      "subtype": "",
      "system": "softswiss",
      "technology": "H5",
      "width": ""
    },
    "ru": {
      "alias": "asgardian-stones-netent",
      "allow_freespins": true,
      "allowed_countries": [],
      "bonus_no_play": false,
      "categories": [
        {
          "alias": "slots",
          "id": "7371929f-fa09-4239-873f-99bdc24034e5",
          "title": "Slots"
        }
      ],
      "codes": {
        "desktop": "netent:asgardianstones_not_mobile_sw",
        "external_desktop": "",
        "external_mobile": "",
        "mobile": "netent:asgardianstones_not_mobile_sw"
      },
      "data": {
        "bonus_no_play": false,
        "payout": 96.31,
        "rollover_allowed": 100
      },
      "demo": true,
      "description": "",
      "game_id": 23783,
      "height": "",
      "images": {
        "bg": [
          "https://cdn.softswiss.net/b/netent/asgardianstones_not_mobile_sw.jpg"
        ],
        "image": [
          "https://cdn.softswiss.net/i/s3/netent/asgardianstones_not_mobile_sw.png"
        ],
        "imagex2": [],
        "promo": []
      },
      "jackpot": false,
      "jackpotCode": "",
      "name": "Asgardian Stones",
      "new": false,
      "parent_id": "",
      "provider": {
        "alias": "netent",
        "id": "a46dcd5d-60b6-4dad-b853-e35481bb8202",
        "name": "Netent",
        "provider_id": 2880
      },
      "provider_view": true,
      "ratio": "",
      "restricted_countries": [
        {
          "code": "AF",
          "id": "cc4afa07-85cc-499e-b242-8098a688019f",
          "name": "Afghanistan"
        },
        {
          "code": "AL",
          "id": "01548a42-34b9-4de6-ac55-4e19632d3f54",
          "name": "Albania"
        },
        {
          "code": "AO",
          "id": "34b560a6-6e48-433d-ba9f-fb9393fec7c7",
          "name": "Angola"
        },
        {
          "code": "AU",
          "id": "3d6bde44-5bb3-417d-a7df-16492fe1f561",
          "name": "Australia"
        },
        {
          "code": "BE",
          "id": "80316424-ec2d-4a9b-b1eb-1fd68a42f5d7",
          "name": "Belgium"
        },
        {
          "code": "BG",
          "id": "c878aa6a-d116-497f-a181-6630cf2850ed",
          "name": "Bulgaria"
        },
        {
          "code": "CH",
          "id": "b9d5076e-97ab-4080-ae49-a9abf88e94b7",
          "name": "Switzerland"
        },
        {
          "code": "CZ",
          "id": "3a417bee-f7e2-4b31-b28f-a90ea5d6d5ef",
          "name": "Czech Republic"
        },
        {
          "code": "DK",
          "id": "cc666a3a-2f64-4b45-8ddb-588bebacb64b",
          "name": "Denmark"
        },
        {
          "code": "DZ",
          "id": "6f0e59b1-2a09-4373-8be8-35b390045a85",
          "name": "Algeria"
        },
        {
          "code": "EC",
          "id": "4840aa3b-2288-4d5a-9499-d975d3209ffc",
          "name": "Ecuador"
        },
        {
          "code": "EE",
          "id": "3c2d65b8-70b3-4292-b7bf-17fa42ab6250",
          "name": "Estonia"
        },
        {
          "code": "ES",
          "id": "684b87b3-bbce-434b-8517-617a4b4503a3",
          "name": "Spain"
        },
        {
          "code": "FR",
          "id": "7da640d6-f219-4d65-9fae-aedee6bb3a79",
          "name": "France"
        },
        {
          "code": "GB",
          "id": "83754448-ba9a-494f-9843-be1a616d4b86",
          "name": "United Kingdom"
        },
        {
          "code": "GY",
          "id": "4bb290e4-655b-43a7-8220-276d064856c8",
          "name": "Guyana"
        },
        {
          "code": "HK",
          "id": "19de1f8a-2a70-46c4-ba45-6df4aaad8caf",
          "name": "Hong Kong"
        },
        {
          "code": "ID",
          "id": "a89ca69b-eefc-4c2f-a980-03f73e4bfa37",
          "name": "Indonesia"
        },
        {
          "code": "IL",
          "id": "e616cde6-48ca-4fb3-a0cb-93586d1d8cac",
          "name": "Israel"
        },
        {
          "code": "IQ",
          "id": "6ccd7e1a-d930-4038-8f98-8f4947a3229a",
          "name": "Iraq"
        },
        {
          "code": "IR",
          "id": "9d7efe9b-4dc1-49fc-bb84-7d53b92577a6",
          "name": "Iran, Islamic Republic of"
        },
        {
          "code": "KH",
          "id": "8a2ce537-72fd-47bb-abf1-599e7baf410a",
          "name": "Cambodia"
        },
        {
          "code": "KP",
          "id": "db2843bc-dbb5-4ae9-9031-cd1fa8704d8b",
          "name": "Korea, Democratic People's Republic of"
        },
        {
          "code": "KR",
          "id": "aea0f0ff-6a52-4fa9-b17c-762007f9634a",
          "name": "Korea"
        },
        {
          "code": "KW",
          "id": "3d6ccf01-43f7-46a7-9bfb-c160707ca74a",
          "name": "Kuwait"
        },
        {
          "code": "LA",
          "id": "e854230e-3574-483e-a780-a87de4d9cce7",
          "name": "Laos"
        },
        {
          "code": "LT",
          "id": "21503527-ddde-4644-a187-8453b6cd7231",
          "name": "Lithuania"
        },
        {
          "code": "LV",
          "id": "fe23e445-1506-4875-95f1-e879a49ce3e7",
          "name": "Latvia"
        },
        {
          "code": "MM",
          "id": "29967dc6-529a-4e4f-bb61-e1848fc0c6bd",
          "name": "Myanmar"
        },
        {
          "code": "MX",
          "id": "6fe5eaa7-9b23-444e-b2c1-7e95991bc7b4",
          "name": "Mexico"
        },
        {
          "code": "NA",
          "id": "2610dca4-04dc-4e73-914f-ed9601774f16",
          "name": "Namibia"
        },
        {
          "code": "NI",
          "id": "c05ddba4-d2d2-4234-9271-f48c5082c1db",
          "name": "Nicaragua"
        },
        {
          "code": "PA",
          "id": "e4992ac3-94f8-47c9-9b0c-4d35b4976af8",
          "name": "Panama"
        },
        {
          "code": "PG",
          "id": "806dd3e5-24be-4ccd-bf58-f8b38b180c7a",
          "name": "Papua New Guinea"
        },
        {
          "code": "PH",
          "id": "178bdf7a-f9cf-4d30-9497-bbb1683c429f",
          "name": "Philippines"
        },
        {
          "code": "PK",
          "id": "c1177579-b4ae-4ac4-a78c-d33e0c75b6bc",
          "name": "Pakistan"
        },
        {
          "code": "PT",
          "id": "9da68cb1-1970-42e0-8f47-1099a0c8bffe",
          "name": "Portugal"
        },
        {
          "code": "RO",
          "id": "bb9daf31-eb37-4c7f-94e9-c7cbf2a9232e",
          "name": "Romania"
        },
        {
          "code": "RS",
          "id": "68a1ae50-dc73-46e4-80e7-378b0cfb397d",
          "name": "Serbia"
        },
        {
          "code": "SD",
          "id": "d2ce5fb4-b639-4162-bd35-ca632d7fb1e8",
          "name": "Sudan"
        },
        {
          "code": "SE",
          "id": "1a3c481c-2bf5-40dd-965b-dcb3714a5759",
          "name": "Sweden"
        },
        {
          "code": "SG",
          "id": "66d5657a-8adb-4dd2-b33d-40441345477d",
          "name": "Singapore"
        },
        {
          "code": "SY",
          "id": "a429f555-d84a-4d32-855c-2d58ee833218",
          "name": "Syria"
        },
        {
          "code": "TW",
          "id": "ef11dd25-7f76-4e51-926b-acd28f2edf3d",
          "name": "Taiwan"
        },
        {
          "code": "UG",
          "id": "f1b246ce-108b-4c53-a4aa-3e9b80b5d0c2",
          "name": "Uganda"
        },
        {
          "code": "US",
          "id": "13bfa166-674e-4071-ae43-761fa191ef1a",
          "name": "United States"
        },
        {
          "code": "YE",
          "id": "b893f22c-ec3e-4791-b1ca-ae93941b289f",
          "name": "Yemen"
        },
        {
          "code": "ZW",
          "id": "242e4b5a-d317-47d5-be5f-b83bc1274b8c",
          "name": "Zimbabwe"
        },
        {
          "code": "IT",
          "id": "8cad9b1b-5082-405f-a3f4-fa3b8e82a437",
          "name": "Italy"
        }
      ],
      "seo": {
        "description": "Asgardian Stones игровой автомат от провайдера Netent. Играйте бесплатно или на настоящие деньги в Asgardian Stones в казино RollingSlots",
        "title": "Играйте в Asgardian Stones слот | Бонус до 50000 RUB + 100 бесплатных вращений | RollingSlots"
      },
      "subtype": "",
      "system": "softswiss",
      "technology": "H5",
      "width": ""
    }
  },
  "hash": 1379765974,
  "id": "703e5d34-153c-465e-afd5-73dd2ad435bd",
  "stamp": "2021-06-25T18:08:01.148Z",
  "status": "active",
  "version": 53,
  "versions": [
    34,
    35,
    36,
    37,
    38,
    39,
    40,
    41,
    42,
    43,
    44,
    45,
    46,
    47,
    48,
    49,
    50,
    51,
    52,
    53
  ]
}
11

Hi @Lenty_Me,

I could not reproduce the issue with this document. Here’s what I did:
Created 2 documents with the content you provided and adjusted their id / data.en.provider.id values to point to each other.
document1:

{ 
  "id": "703e5d34-153c-465e-afd5-73dd2ad435bd", 
  "data": { "en": { "provider":{ "id": "a46dcd5d-60b6-4dad-b853-e35481bb8202", ... } } }
   ... <other fields> ...
}

document2:

{ 
  "id": "a46dcd5d-60b6-4dad-b853-e35481bb8202" , 
  "data": { "en": { "provider":{ "id": "703e5d34-153c-465e-afd5-73dd2ad435bd", ... } } }
  ... <other fields> ...
}

Then I ran your original join query:

SELECT col_doc_pr.data.en.alias
FROM `deploy` col_doc
JOIN `deploy` col_doc_pr ON col_doc_pr.id = col_doc.data.en.provider.id
 WHERE col_doc._type="game" AND col_doc._scope="bread"

The query ran successfully and returned 2 objects (aliases) as expected.

I then tried to see whether I could get the error you’re getting by modifying the contents of the “data” field. I changed it in the second document to be an array containing a single object as follows:
document2:

{ 
  "id": "a46dcd5d-60b6-4dad-b853-e35481bb8202" , 
  "data": [ { "en": { "provider":{ "id": "703e5d34-153c-465e-afd5-73dd2ad435bd", ... } } } ]
  ... <other fields> ...
}

Then I ran the same join query and it failed with that exact error:

"code": 23023,
"msg": "Type mismatch: expected value of type object, but got the value of type array (in line 1, at column 23)"

The Couchbase Server version I used is 6.6.2.

Could you double check your data to make sure that there are no arrays in the “data” field?

12

Did you use Analytics n1ql query ?
In the simple bucket Query n1ql query execute without errors.

13

Really we have a document with data as array. Thank you for help