0

I have a data set that has an array property. Example when I select * from c:

[
  {
    "id": "1",
    "props": []
  },
  {
    "id": "2",
    "props": ["a"]
  },
  {
    "id": "3",
    "props": ["b"]
  }
]

I want to run this query to order by the props column:

select * from c order by c.props

BUT it returns 0 records because the first array item is empty (if I drop the order by it works fine and returns the three records). I can do this to attempt to work around it:

select c.* as item, props = [] ? '' : props[0] as orderCol from c

Returns all three items (has no order by) but when I try to apply the order by like this:

select c.* as item, props = [] ? '' : props[0] as orderCol from c order by c.orderCol

It returns 0 records. What am I doing wrong? (I dont want a where clause to filter the empty props records). Basically, I can't order by my custom selected field "orderCol" when I want to.

NOTE: using order by c.id works fine!

Thanks for any pointers in advance!

Rob
  • 6,819
  • 17
  • 71
  • 131
  • 1
    Does the array property only ever have a single item? Strange why an order by would cause 0 results in general, seems broken, although sorting by an array property isn't something I'd say is common or expected given there could be multiple items. – James Dec 17 '19 at 12:52
  • Want to default sort by first array item really - just doesn't seem to be working at all – Rob Dec 17 '19 at 12:57
  • presumably you get an error though and not just zero results? – James Dec 17 '19 at 13:07
  • Get an error when trying to use the custom field yea – Rob Dec 17 '19 at 13:17
  • Not sure how ordering would work with an array, since arrays can contain a variable number of items - that doesn't really make sense. As for missing items: currently indexes only include documents that contain the property in question; see my answer [here](https://stackoverflow.com/a/59071480/272109), related to this. – David Makogon Dec 17 '19 at 13:45
  • I was able to work around the empty array fine I just couldnt use the custom property in my sort - has anyone got this going? – Rob Dec 17 '19 at 13:51

1 Answers1

1

Tested your scenario and get the same result.It seems that order by could be mapped to a document path and can't contain not-defined column. I suggest you using below sql:

select c.id,IS_DEFINED(c.props[0]) ? c.props[0]:"" as propsVal from c

Output:

enter image description here

Then sort the results by coding,such as Array.Sort() method in .NET.

Jay Gong
  • 23,163
  • 2
  • 27
  • 32