SQL to Json - Array of Objects to Array of Values in SQL 2016

SQL to JSON - array of objects to array of values in SQL 2016

Thanks! The soultion we found is converting into XML first -

SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"' 
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER 

How to create array of object in JSON from data in SQL table by SQL query?

I think this will work...

select [Name] as [ID.FirstName], schoolName as [School.School_Name] 
from Details 
for json path, root('Students')

I looked at the MS docs for the "for json" command. I don't think you need to split up your select fields the way you did. Also, I added the brackets around "Name" because memory is telling me that it's a reserved word, and you can force it to treat it as a field name with the brackets.

How to return an array in a JSON object from SQL Server

You can embed an array of objects into the cars property by using a subquery inside json_query(), such as with the following:

select
  FirstName, 
  ID,
  json_query((
    select
      CarLicense,
      CarType,
      CarSystem as SourceSystem
    from dbo.cars carsInner
    where carsInner.ID=carsOuter.ID
    for json path
  )) as cars
from dbo.cars carsOuter
for json path;

How to convert JSON object rows into JSON array in MySQL?

You can use MySQL JSON_ARRAYAGG aggregation function:

SELECT JSON_ARRAY_AGG(JSON_OBJECT('uniqueId', uniqueId, 'name', name)) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

Try it here.

Between clause on Array of numerical values in row for JSON type column

You may use an APPLY operator and an OPENJSON() call to parse the stored JSON and apply the appropriate WHERE clause:

SELECT * 
FROM (VALUES (N'[{"key": 12}, {"key": 13}, {"key": 19}]')) v (JsonData)
CROSS APPLY OPENJSON(v.JsonData) WITH ([key] int '$.key') j
WHERE j.[key] BETWEEN 5 AND 12

---

Related Topics