Find specific field in Json with SQL Server

Question

I'm working with SQL Server 2019 and pulling data from MercadoLibre API as JSON. Here's an example of a simple detail product:

https://api.mercadolibre.com/items?ids=MLA1133834371&attributes=title,price,original_price,id,tltle,last_updated,attributes.id,attributes.value_name

This outputs a Json that looks like this:

{"code":200,"body":{"price":835,"original_price":null,"id":"MLA1133834371","last_updated":"2023-01-20T00:46:01.000Z","attributes":[{"id":"BRAND","value_name":"Energizer"},{"id":"CELL_BATTERY_CAPACITY","value_name":null},{"id":"CELL_BATTERY_COMPOSITION","value_name":"Alcalino"},{"id":"CELL_BATTERY_SHAPE","value_name":"Rectangular"},{"id":"CELL_BATTERY_SIZE","value_name":"9V"},{"id":"COLOR","value_name":"Plateado"},{"value_name":null,"id":"ENVIRONMENTAL_MANAGEMENT_PLAN_AUTHORIZATION"},{"id":"GTIN","value_name":"039800013613"},{"value_name":"Sí","id":"IS_FLAMMABLE"},{"id":"IS_RECHARGEABLE","value_name":"No"},{"value_name":"Nuevo","id":"ITEM_CONDITION"},{"value_name":"MAX","id":"LINE"},{"id":"MAX_LOAD_HOLDING_TIME","value_name":"5 años"},{"id":"MODEL","value_name":"522"},{"id":"NOMINAL_VOLTAGE","value_name":"9V"},{"value_name":"3.4 cm","id":"PACKAGE_HEIGHT"},{"id":"PACKAGE_LENGTH","value_name":"6.6 cm"},{"id":"PACKAGE_WEIGHT","value_name":"140 g"},{"id":"PACKAGE_WIDTH","value_name":"6.6 cm"},{"id":"PRODUCT_FEATURES","value_name":"Con vencimiento"},{"id":"RECOMMENDED_USES","value_name":"Control remoto,Juguetes,Linterna,Reloj"},{"id":"SALE_FORMAT","value_name":"Unidad"},{"id":"SELLER_SKU","value_name":"ENE1XALC9V"},{"value_name":"1","id":"UNITS_PER_PACK"},{"id":"WITH_MERCURY","value_name":"No"}],"title":"Pila 9v Energizer Max 522 Rectangular - 1 Unidad"}}

Problem is that the "attributes" fields comes in different order for every item you query, so far I've tried:

DECLARE @url VARCHAR(max) = 'https://api.mercadolibre.com/items?ids=MLA1133834371&attributes=title,price,original_price,id,tltle,last_updated,attributes.id,attributes.value_name'
DECLARE @token as int;
DECLARE @respuesta as VARCHAR(8000);
DECLARE @ret INT;

EXEC sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'send'
EXEC @ret = sp_OAMethod @token, 'responseText', @respuesta OUTPUT

SET @respuesta =  TRIM('[]' FROM @respuesta)

SELECT JSON_VALUE(@respuesta,'$.body.id'), 
JSON_VALUE(@respuesta,'$.body.title'), 
JSON_VALUE(@respuesta,'$.body.price'),
JSON_VALUE(@respuesta,'$.body.last_updated'),
JSON_VALUE(@respuesta,'$.body.attributes[7].id'),
JSON_VALUE(@respuesta,'$.body.attributes[7].value_name')

EXEC sp_OADestroy @token

How can i find this GTIN field without knowing it's position in the array?

Thanks

Martin Smith · Accepted Answer · 2023-01-23 22:15:30Z

1

You can use OPENJSON

SELECT value_name
FROM OPENJSON(@respuesta, '$.body.attributes')
WITH (id VARCHAR(100), value_name VARCHAR(100))
WHERE id = 'GTIN'

You might also be better off using that for the other values too. If you are interested in additional attributes you can consider PIVOT (dbfiddle)

SELECT title, price, last_updated, attrib.*
FROM OPENJSON(@respuesta, '$.body')
WITH (title VARCHAR(100), 
     price INT, 
     last_updated DATETIME2(0), 
     attributes NVARCHAR(MAX) AS JSON)
OUTER APPLY
(
SELECT *
FROM OPENJSON(attributes)
WITH (id VARCHAR(100), value_name VARCHAR(100))
PIVOT (MAX(value_name) FOR id IN (GTIN, SELLER_SKU)) P
) attrib

edited Jan 23, 2023 at 22:15

answered Jan 23, 2023 at 22:06

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

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

1 Comment

Juan Pablo Humani Over a year ago

oh...that's magic but yet i dont understand the sintaxis and tools you are using...I mean, you solve my issue, but still i can't understand it. I'm in SQL for the last 5 months and some clauses are quite hard. If you want, you can enlighten me...Thanks in advantage (sorry if english sucks, im from Argentina)

Collectives™ on Stack Overflow

Find specific field in Json with SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related