Get values from JSON - SQL Server

Question

I have the following JSON:

[
  {
    "email_id": 1598819368,
    "email": "[email protected]"
  },
  {
    "email_id": 1598819369,
    "email": "[email protected]"
  },
  {
    "email_id": 1598819370,
    "email": "[email protected]"
  },
  {
    "email_id": 1598819371,
    "email": "[email protected]"
  }
]

How do I return a list of just the email addresses using SQLServer 2017?

-------------------
   emailaddress   
-------------------
  [email protected]
  [email protected]
  [email protected]
  [email protected]

I've been trying to use JSON_VALUE or JSON_QUERY but I either only get one email address or NULLs.

Thank you!

Answer 1

This should do it

declare @json         nvarchar(max)=N'[
  {
    "email_id": 1598819368,
    "email": "[email protected]"
  },
  {
    "email_id": 1598819369,
    "email": "[email protected]"
  },
  {
    "email_id": 1598819370,
    "email": "[email protected]"
  },
  {
    "email_id": 1598819371,
    "email": "[email protected]"
  }
]'

select email from openjson(@json) with (email_id            bigint,
                                        email               nvarchar(200));

Results

email
[email protected]
[email protected]
[email protected]
[email protected]