logo
down
shadow

Array to json and row to json in SQL Server 2016


Array to json and row to json in SQL Server 2016

By : user2185289
Date : November 21 2020, 04:01 AM
Any of those help , I think you can try to use FOR JSON AUTO with ROOT
code :
SELECT UserName,EmailId,UserId,UserType,User_Level
FROM T
FOR JSON AUTO,ROOT('USER')


Share : facebook icon twitter icon
Accessing JSON Array in SQL Server 2016 using JSON_VALUE

Accessing JSON Array in SQL Server 2016 using JSON_VALUE


By : Renee
Date : March 29 2020, 07:55 AM
I hope this helps you . I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code - , To get all from phoneNumbers:
code :
DECLARE @json nvarchar(max)=
    '{
      "firstName": "John",
      "lastName" : "doe",
      "age"      : 26,
      "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
      },
      "phoneNumbers": [
        {
          "type"  : "iPhone",
          "number": "0123-4567-8888"
        },
        {
          "type"  : "home",
          "number": "0123-4567-8910"
        }
      ]
    }'

    SELECT [Type], [Number]
    FROM OPENJSON( @json, '$.phoneNumbers' ) 
    WITH ([Type] NVARCHAR(25) '$.type', [Number] NVARCHAR(25) '$.number');
Get length of json array in SQL Server 2016

Get length of json array in SQL Server 2016


By : user6684040
Date : March 29 2020, 07:55 AM
Any of those help You know about the new JSON_ support in SQL Server 2016 so let's say I have this data in a row , Using a table instead of a variable:
code :
SELECT count(priceLineLists.RoomTypeId)
FROM Mytable
CROSS APPLY OPENJSON (JsonDataCol, N'$.BasePriceLineList')
  WITH (
    RoomTypeId int)
      AS priceLineLists
How can I get json array values in result using the new tsql json from server 2016?

How can I get json array values in result using the new tsql json from server 2016?


By : Rob Ogus
Date : March 29 2020, 07:55 AM
I hope this helps you . Your query is almost exact, I think that you just need to remove the second with clause (since there is no ip field in your inner array) and select the value column from ips instead of ip:
code :
declare @json nvarchar(max) =  '{
  "state": "Succeeded",
  "ip": [
    "137.117.198.99","137.117.198.100"
  ]  
}';

select
    network.[state], 
    ips.value as ip
from  openjson (@json)
with
(
    [state] nvarchar(100),
    ip nvarchar(max) as json
)
as network
cross apply openjson (network.ip)
as ips
;
SQL Server 2016 select where in json array

SQL Server 2016 select where in json array


By : Bigboy Santos
Date : March 29 2020, 07:55 AM
seems to work fine You need to use OPENJSON function for reading Names array. You can use this query.
code :
SELECT * FROM [TestTable] T
INNER JOIN [JsonTable] AS J ON T.[Name] IN (SELECT value FROM OPENJSON(J.[JsonData],'$.Names'))
SQL Server 2016 for JSON output integer array

SQL Server 2016 for JSON output integer array


By : Deepak Bansal
Date : March 29 2020, 07:55 AM
Hope that helps In AdventureWorks 2016 CTP3 JSON sample you can find a function that can clean array of key:value pairs and create array od values:
Related Posts Related Posts :
  • Oracle 11 query to combine multiple rows into 1 row
  • How to compare 2 converted dates in sql
  • From a bit complex SQL statement to preparedStatement
  • How to count distinct logins
  • SQL Server - Operand type clash: numeric is incompatible with datetimeoffset
  • Splitting 24 hours into 96 equal 15 minute intervals in T-SQL
  • WHERE clause not hitting on LEFT JOIN
  • SQL Pivot String Data
  • Redshift - Find % as compared to total value
  • How to find count of N columns in a table
  • SQL statement giving no result
  • IN condition is not working when the data is like 'Test','Test' from a result query
  • SUM of COUNT with only certain variables
  • How to get all products with at least X reviews in SQL?
  • sql Group by columns to the same row without join
  • Check if an integer is within the next 5 increments in SQL, when the numbers can wrap
  • Case and When query give me both column filled
  • How to check JSON string on contains field AND like value?
  • Find uncovered periods without exploding each combination
  • How to compare date to format date on oracle
  • SQL Server : duplicate rows when joining tables and inserting new data?
  • Oracle - Combine timestamp with date and remove duplicates
  • Modifying SQL code to pick the Dropdown list instead of constant text
  • How to get max value of column values in a record ? (BigQuery)
  • Elasticsearch filter after aggregation
  • Teradata: varchar to timestamp 2
  • specific row number with all the fields
  • INSERT INTO SELECT query in SQL where primary key is used and multiple duplicates are in another table
  • SQL Query - Eliminate records
  • What is wrong with this SQL join query
  • Hybris Flexible search union query to fetch products
  • Stale Data Count
  • Adding new column of total_event
  • Query to Skip the first Delimiter and select the second Delimiter In SQL
  • Very slow (12+ hours) large table joins in postgres
  • LINQ query for specific group
  • SQL - return xpath for multiple XML results
  • Can't compile my trigger in Apex Oracle SQL/PL PLS-00103
  • Select 1 field from a group
  • Airflow + pandas read_sql_query() with commit
  • Get higest value of each relation
  • SQL - select limit column2 values for distinct column 1
  • Compilation failed, PLS-00103 pl/sql
  • SQL Query for all IDs where all rows with that ID match a value in another column
  • How to correctly translate sql-code in Delphi
  • How do I join 2 tables to allocate items?
  • PARTITION BY in CASE doesn't work with several AND statements
  • SQL. How to combine two records with the same ID into one line based on value's in a column
  • How to update tables from an external Access database with identical tables?
  • Trying to find records between two dates on one table and some information on another
  • Why does adding ORDER BY drastically speed up query?
  • SQL syntax explanation (CAST, INSERT, DATE)
  • My query with <> to / NOT IN () clause not returning any values?
  • Comparing 2 tables
  • How do I filter my table to show the result in Oracle?
  • SQL Server 2016: How to read different substrings from a text with special characters
  • Oracle - Left Anti Join on same table
  • Maximum Capacity for BULK INSERT from files such as CSV
  • simple JSON array to Rows
  • SQL Server - matching attributes query
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org