Select results from multiple array elements in Json document with Sql Server 2016's OPENJSON function

Question

Is it possible to combine parts of a json document from multiple array elements into a single result in Sql Server 2016?

Given this json:

{
  "fruit": {
    "types": [
      {
        "possible": [ "Apples", "Bananas", "Pears" ],
        "category": "Basic"
      },
      {
        "possible": [ "Oranges", "Grapefruit", "Lemons", "Limes" ],
        "category": "Citrus"
      },
      {
        "possible": [ "Blueberries", "Strawberries", "Cherries" ],
        "category": "Berries"
      }
    ]
  }
}

I'd like to see a single result of all the values from the possible element:

results
-----
Apples
Bananas
Pears
Oranges
Grapefruit
Lemons
Limes
Blueberries
Strawberries
Cherries

I've gotten close doing this:

SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[0].possible'))
UNION
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[1].possible'))
UNION
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[2].possible'))

But this relies on tying the query to the number of elements in the array. Is there a way to do it without having to specify each array element individually? Something like this (neither of these are valid expressions):

SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[].possible'))

or

SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types.possible'))

Is this where I should be doing a CROSS APPLY?

David Browne - Microsoft · Accepted Answer · 2017-07-31 23:24:38Z

7

Is this where I should be doing a CROSS APPLY?

Yep.

declare @json nvarchar(max)='
{
  "fruit": {
    "types": [
      {
        "possible": [ "Apples", "Bananas", "Pears" ],
        "category": "Basic"
      },
      {
        "possible": [ "Oranges", "Grapefruit", "Lemons", "Limes" ],
        "category": "Citrus"
      },
      {
        "possible": [ "Blueberries", "Strawberries", "Cherries" ],
        "category": "Berries"
      }
    ]
  }
}
'

select v.value
from openjson(@json, '$.fruit.types') t
cross apply openjson(t.value,'$.possible') v

outputs

value
---------
Apples
Bananas
Pears
Oranges
Grapefruit
Lemons
Limes
Blueberries
Strawberries
Cherries

(10 row(s) affected)

answered Jul 31, 2017 at 23:24

David Browne - Microsoft

90.8k7 gold badges52 silver badges87 bronze badges

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

1 Comment

Dave Slinn Over a year ago

Wonderful. Thank you.

Collectives™ on Stack Overflow

Select results from multiple array elements in Json document with Sql Server 2016's OPENJSON function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related