0

I have JSON like this:

{
  "_key": {
    "id": "3b8d1ed7-c3ec-32ec-a083-2ddc17152e94",
    "rootId": "15c85327-9628-3685-b84a-375b546ba92a",
  },
  "employeeInfo": {
    "idNumber": "3",
    "gender": "Male",
    "age": 20,
    ....
  },
  "product": {
    "plan": "prod",
    "class": "1",
    "_type": "Product",
    ...
  }
}

And I want to receive new JSON with desired fields. I'm using next query:

SELECT 
    '{ "employeeInfo": {"age: ' + 
    JSON_VALUE(info, '$.employeeInfo.age') + ', "gender": ' + 
    JSON_VALUE(info, '$.employeeInfo.gender') + ' }, ' +
    '"product": {"plan": ' + JSON_VALUE(info, '$.product.plan') + '} }' as info
FROM 
    item.[Item] AS c
INNER JOIN 
    (SELECT "rootId", MAX("revisionNo") AS maxRevisionNo 
     FROM item."Item"
     WHERE "rootId" = 'E3B455EF-D48E-338C-B6D4-FFD8B41243F9' 
     GROUP BY "rootId") AS subquery ON c."rootId" = subquery."rootId";

And I get response with such columns:

{ "employeeInfo": {"age: 38, "gender": Female }, "product": {"plan": Plan 1} }

But it seems JSON_VALUE doesn't return type, so 'Female' and 'Plan 1' are without quotes. I don't know what fields will be requested, so I can't add quotes myself. I will receive field names in runtime and want to build request dynamically. How can I execute a query to return values with their types? I.e. I expect the next response:

{ "employeeInfo": {"age: 38, "gender": "Female" }, "product": {"plan": "Plan 1"} }

I already have the solution for Postgres:

SELECT jsonb_strip_nulls(json_build_object('employee_info', json_build_object('age', c."info"->'employeeInfo' -> 'age', 'gender', c."info"->'employeeInfo' -> 'gender'), 'product', json_build_object('plan', c."info"->'product' -> 'plan'))::jsonb) as info ...

And also need to build a request for sql-server. I will build this request dynamically when receive field names.

I'm using this SQL Server version:

Microsoft Azure SQL Edge Developer (RTM) - 15.0.2000.1552 (ARM64)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Valeriy K.
  • 2,616
  • 1
  • 30
  • 53
  • `{ "employeeInfo": {"age: 38, "gender": Female }, "product": {"plan": Plan 1} }` isn't valid JSON - are you sure that's what you get? – Paul Maxwell Sep 02 '23 at 03:16
  • Not sure why you're trying to hand-construct JSON when SQL Server has built-in functions to extract and create JSON data. Have you tried reading the SQL Server documentation for [`FOR JSON`](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server), [`JSON_QUERY`](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql), [`JSON_VALUE`](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql) and [`OPENJSON`](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql) yet? – AlwaysLearning Sep 02 '23 at 04:27

0 Answers0