A simple query to get list from a document

SQL++
1

Hi,

I’m a complete newbie, I have a document like so:

{
“jason-test”: {
“DateCreated”: 1517384479500121000,
“XAccountId”: “1234”,
“XApiKey”: “”,
“XRequestId”: “/JKGD1OYyUWFuM5BgWzQ1A”,
“XTargetAccountId”: “”,
“XUserId”: “”,
“categories”: [
{
“confidence”: 0.66,
“name”: “/Beauty & Fitness/Fitness”
},
{
“confidence”: 0.53,
“name”: “/Computers & Electronics”
}
],

I want to pull out all categories elements for a given XAccountId, but when I try

SELECT “categories” FROM jason-test where “XAccountId” = “1234”

I get an empty result.

2

You don’t give quotes around field names. If needed give back-ticks (`)

SELECT categories FROM `jason-test` where `XAccountId` = "1234";
3

you need back-quote only when you have special characters like hyphen (in case of jason-test). For others (categories, XAccountId), you don’t need one.

SELECT categories FROM `jason-test` where XAccountId = “1234”

4

Thanks! Now I get the following:

[
{
“$1”: “categories”
},
{
“$1”: “categories”
}
]

How to I get the actual contents of the “categories” elements?

5

please post exact query you executed. You might have double quotes around categories. That means it is constant string not retrieving a field in the document

7

Ok that was it! I needed to remove the double quotes