Document DB SQL Api - unable to query json property with name 'value' and its value is integer

Question

  {
    "id": "3d50809d-d631-4576-925a-7232d9ef9338",
    "TrackingId": "3d50809d-d631-4576-925a-7232d9ef9338",
    "records": [
      {
        "measurementTime": {
          "value": "2018-04-01 10:00:00.000",
          "unit": "datetime"
        },
        "systolic": {
          "value": 114,
          "unit": "mm(hg)"
        },
        "diastolic": {
          "value": 88,
          "unit": "mm(hg)"
        }
      }
    ]
  },
  {
    "id": "c5f3bd10-1959-4407-92cb-6d9548950f2c",
    "TrackingId": "c5f3bd10-1959-4407-92cb-6d9548950f2c",
    "records": [
      {
        "measurementTime": {
          "value": "2018-04-02 10:00:00.000",
          "unit": "datetime"
        },
        "systolic": {
          "value": 122,
          "unit": "mm(hg)"
        },
        "diastolic": {
          "value": 91,
          "unit": "mm(hg)"
        }
      }
    ]
  }

I am trying to querying documents in Azure Docuemnt DB having this sample format. (please don't try to validate JSON or property names in query, i am giving just a stripped of version to understand hierarchy)

I am successfully querying some properties, but i am getting error when trying to fetch the integer value under systolic--> value or diastolic--> value with the query mentioned below.

Success Query:

SELECT 
c.id,
c.TrackingId,
c.records[0].measurementTime["value"]

Failure Query:

SELECT 
c.id,
c..TrackingId,
c.records[0].measurementTime["value"],
c.records[0].systolic["value"],
c.records[0].diastolic["value"]

the keyword usage of ["value"] is working for measuremenTtime but not working for systolic or diastolic

Error details:

Message : "Object creation error, property name 'value' specified more than once.

code:400

severity: Error

Jay Gong · Accepted Answer · 2018-04-13 02:17:35Z

1

Kailash Ravuri, in fact, your issue is not related to field name or field type. Based on your query sql, your result data have 3 fields named 'value',it's not allowed. You just need to add an alias to 3 fields and everything will be ok.

SELECT 
c.id,
c.TrackingId,
c.records[0].measurementTime["value"] as measurementTimeValue,
c.records[0].systolic["value"] as systolicValue,
c.records[0].diastolic["value"] as diastolicValue FROM c

Hope it helps you.

answered Apr 13, 2018 at 2:17

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kailash Ravuri Over a year ago

Thanks for the help @jay gong. The query worked like a charm, but i wanted to know more about the error. Why is it 3 fields named 'value' throwing error, are there any documentation for these kinds or rules, So that i can learn more about.

Jay Gong Over a year ago

@KailashRavuri I think this is a basic rule of SQL query, when the query field has a duplicate of the case need to use as to distinguish each other. You could refer to this case: stackoverflow.com/questions/12908921/…

Collectives™ on Stack Overflow

Document DB SQL Api - unable to query json property with name 'value' and its value is integer

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related