GET last element of array in json column of my Transact SQL table

Question

Thanks for helping.

I have my table CONVERSATIONS structured in columns like this :

[ ID , JSON_CONTENT ]

In the column ID i have a simple id in Varchar

In the column JSON_CONTENT i something like this :

{
    "id_conversation" : "25bc8cbffa8b4223a2ed527e30d927bf", 
    "exchanges": [
        {
            "A" : "...",
            "B": "..."
        },
        {
            "A" : "...",
            "B": "..."
        },
        {
            "A" : "...",
            "Z" : "..."
        }
    ]
}

I would like to query and get the id and the last element of exchanges :

[ ID , LAST_ELT_IN_EXCHANGE_IN_JSON_CONTENT]

I wanted to do this :

select TOP 3 ID, JSON_QUERY(JSON_CONTENT, '$.exchange[-1]')
from CONVERSATION

But of course Transact SQL is not Python.

I saw theses answers, but i don't know how to applicate to my problem.

Select last value from Json array

Thanks for helping <3

Answer 1

If I understand you correctly, you need an additional APPLY operator and a combination of OPENJSON() and ROW_NUMBER(). The result from the OPENJSON() call is a table with columns key, value and type and when the JSON content is an array, the key column returns the index of the element in the specified array:

Table:

SELECT ID, JSON_CONTENT
INTO CONVERSATION
FROM (VALUES
  (1, '{"id_conversation":"25bc8cbffa8b4223a2ed527e30d927bf","exchanges":[{"A":"...","B":"..."},{"A":"...","B":"..."},{"A":"...","Z":"..."}]}')
) v (ID, JSON_CONTENT)

Statement:

SELECT c.ID, j.[value]
FROM CONVERSATION c
OUTER APPLY (
   SELECT [value], ROW_NUMBER() OVER (ORDER BY CONVERT(int, [key]) DESC) AS rn
   FROM OPENJSON(c.JSON_CONTENT, '$.exchanges') 
) j   
WHERE j.rn = 1

Result:

ID  value
------------------------
1   {
            "A" : "...",
            "Z" : "..."
    }

Notice, that -1 is not a valid array index in your path expression, but you can access the item in a JSON array by index (e.g. '$.exchanges[2]').