How to query a field value from Json document with a dot in the field name

SQL++
1

HI

I have a json document with the below structure,

{
“profileData”: {
“consumer.name”: {
“id”: “12345”,
“surname” : “abc”,
“givenName”: “xyz”
}
}

and when I try to fetch surname from the document with the below query, I am not getting the expected result, can any one advise how I can fetch the value when there is dot(.) in the field name?

used the below query :
select profileData.consumer.name.surname from hyz

Please advise the right query to fetch the data.

Thanks in advance.
Ajith

2

Hi @ajith.ramachandran,

For a field that contains the dot, you can escape the field in backticks (`).

Enclosing the consumer.name field in backticks would work for your use case.

select profileData.`consumer.name`.surname from hyz

3

This is throwing a syntax error.

4

What is the error that you are getting?

This is my document in a sample bucket and the corresponding query.

Document

{
“profileData”: {
“consumer.name”: {
“id”: “12345”,
“surname”: “abc”,
“givenName”: “xyz”
}
}
}

SELECT profileData.`consumer.name`.surname
FROM `bucket`
WHERE profileData.`consumer.name`.id=“12345”;

5
1 Like