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!

SteveC · Accepted Answer · 2020-08-31 15:43:04Z

2

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]

answered Aug 31, 2020 at 15:43

SteveC

6,0252 gold badges14 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Get values from JSON - SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related